Function button: Unhide cells between values and hide all other Solved/Closed

Question

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

TrowaD · Answer

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 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

MartaGH · Answer

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

Function button: Unhide cells between values and hide all other

2 responses

Similar discussions

Subscribe To Our Newsletter!