Loop macro to last cell in range

[RPM7]

I have a macro that hides / shows sheets in a workbook, but I have to change the macro every time I add or remove a sheet.
Is there a way to change the range from C2:C41 to end the macro at the last active cell in Col A.
In this example, A41 would be the last active sheet name in the range.

I have the names of all the sheets in Col A and in Col B, I have "hide" or "show".

Sub Show_Hide()
Dim c As Range
    Application.ScreenUpdating = False
    
 For Each c In Worksheets("Directory").Range("C2:C41").Cells
  If c.Value = "Hide" Then
                Sheets(c.Offset(, -2).Value).Visible = False
            Else
                Sheets(c.Offset(, -2).Value).Visible = True
            End If
  Next
        Sheets("Directory").Select
      Application.ScreenUpdating = True
End Sub

I'd greatly appreciate it if someone could assist with the code to automate the range, so I don't have to manually edit it all the time.

Thanks

 

[AnthonyA755]

I understand your question to be regarding your reference range changing in size?

You can use Cell.End(xlDown) to get to the bottom of a range (assuming there are not empty cells within your desired range - this works the same as if you were to hold down ctrl on your keyboard and press the down arrow key).

I have included the new code below with a comment where i have made the change

Sub Show_Hide()
Dim c As Range
    Application.ScreenUpdating = False
    
 For Each c In Worksheets("Directory").Range(Cells(2, 3), Cells(2, 3).End(xlDown)).Cells 'THIS IS WHERE I HAVE MADE THE CHANGE 
  If c.Value = "Hide" Then
                Sheets(c.Offset(, -2).Value).Visible = False
            Else
                Sheets(c.Offset(, -2).Value).Visible = True
            End If
  Next
        Sheets("Directory").Select
      Application.ScreenUpdating = True
End Sub

I have not had a chance to test this specifically, so let me know how you go. Keep in mind this solution will only work if there are no blanks within your desired search range.

 

[AnthonyA755]

I understand your question to be regarding your reference range changing in size?

You can use Cell.End(xlDown) to get to the bottom of a range (assuming there are not empty cells within your desired range - this works the same as if you were to hold down ctrl on your keyboard and press the down arrow key).

I have included the new code below with a comment where i have made the change

Sub Show_Hide()
Dim c As Range
    Application.ScreenUpdating = False
    
 For Each c In Worksheets("Directory").Range(Cells(2, 3), Cells(2, 3).End(xlDown)).Cells 'THIS IS WHERE I HAVE MADE THE CHANGE 
  If c.Value = "Hide" Then
                Sheets(c.Offset(, -2).Value).Visible = False
            Else
                Sheets(c.Offset(, -2).Value).Visible = True
            End If
  Next
        Sheets("Directory").Select
      Application.ScreenUpdating = True
End Sub

I have not had a chance to test this specifically, so let me know how you go. Keep in mind this solution will only work if there are no blanks within your desired search range.

 

[AnthonyA755]

Sorry, i sent my reply twice! Please disregard the second response (or if a mod could delete it, that would be great)

 

[RPM7]

I understand your question to be regarding your reference range changing in size?

You can use Cell.End(xlDown) to get to the bottom of a range (assuming there are not empty cells within your desired range - this works the same as if you were to hold down ctrl on your keyboard and press the down arrow key).

I have included the new code below with a comment where i have made the change

Sub Show_Hide()
Dim c As Range
    Application.ScreenUpdating = False
   
For Each c In Worksheets("Directory").Range(Cells(2, 3), Cells(2, 3).End(xlDown)).Cells 'THIS IS WHERE I HAVE MADE THE CHANGE
  If c.Value = "Hide" Then
                Sheets(c.Offset(, -2).Value).Visible = False
            Else
                Sheets(c.Offset(, -2).Value).Visible = True
            End If
  Next
        Sheets("Directory").Select
      Application.ScreenUpdating = True
End Sub

I have not had a chance to test this specifically, so let me know how you go. Keep in mind this solution will only work if there are no blanks within your desired search range.

Thanks @AnthonyA755, that worked. Thanks for your help.