Report

Function button: Unhide cells between values and hide all other [Solved]

Ask a question MartaGH 2Posts Monday January 23, 2017Registration date January 25, 2017 Last seen - Last answered on Jan 25, 2017 at 02:04 PM by MartaGH
Hello!

I am new to macros and this one is proving to be a bit of a challenge for me.

I am building a template that contains several sections.

What I need: I have all the sections listed one after the other in this spreadsheet with keywords typed in between. One of them, for example, is called "Management Testing". In the row above this section I typed "T*MaTes". On the row below this section, I typed "U*Fin". What I need is a macro that will look for the first word, locate it, then look for the second word, select all rows in between and unhide them while keeping all the other sections hidden. Then I will apply that macro to all the other sections to be able to display one at the time while hiding all the others, depending on which option button gets chosen.

The reason I need a macro based on key words is because the sections may get edited at times, (rows added/deleted, etc) which means that any macro for hiding/unhiding rows based on the row number will fail.

This is what I have come up with so far but it is not working:

Sub ManagementTesting()
Dim 1FirstHit As Long
Dim 1SecondHit As Long

Do While True
1FirstHit = getItemLocation("T*MaTes", Columns(1), , False)
If (1FirstHit = 0) Then Exit Do
1SecondHit = getItemLocation("U*Fin", Range(Cells(lFirstHit + 1, 1), Cells(Rows.Count, 1)), , False)
If (1SecondHit = 0) Then Exit Do
Rows(1FirstHit & ":" & lSecondHit).EntireRow.Hidden = True
EndSub
Not sure where to take it from here... your help and guidance will be very much appreciated!!! Thank you!!!!

Marta
See more 
Helpful
+1
plus moins
Hi Marta,

Where is your question?

You are in need of a macro and then provide that macro.
You say that you will apply that macro to other sections, but since that part of the macro is missing, I'm guessing that is where you are stuck.

Try using keywords that end with a number.
You can then replace the number with a variable and use it in a loop.

Keywords
Top1
Bottom1

Variable
x=1

Rough code sample
Do
"Top" & x Combine keyword with variable
"Bottom" & x
x=x+1 Increase variable
Loop Until x=Highest x value


Hopefully this points you in the right direction.

Best regards,
Trowa
Was this answer helpful?  
Leave a comment
Helpful
+0
plus moins
Hello,

I found the answer. This is the macro I created. Thank you Trowa for your response!

Sub MaTes()
Rows("8:1000").EntireRow.Hidden = True
Dim Index As Integer: Index = 9
Dim I As Integer
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets(1)
For Index = 1 To 1000
If Sheet.Cells(Index, 1).Value2 = "T*MaTes" Then
I = Index + 1
For I = Index To 1000
If Sheet.Cells(I, 1).Value2 = "U*Fin" Then
Sheet.Range(Sheet.Rows(Index), Sheet.Rows(I)).EntireRow.Hidden = False
Exit For
Else: I = I + 1
End If
Next
Else: Index = Index + 1
End If
Next
For Index = 1 To 1000
If Sheet.Cells(Index, 1).Value2 = "A*Sum" Then
I = Index + 1
For I = Index To 1000
If Sheet.Cells(I, 1).Value2 = "T*Mates" Then
Sheet.Range(Sheet.Rows(Index), Sheet.Rows(I)).EntireRow.Hidden = True
Exit For
Else: I = I + 1
End If
Next
Else: Index = Index + 1
End If
Next

End Sub
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!