Loop macro to last cell in range

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
182
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".

VBA Code:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

AnthonyA755

New Member
Joined
Apr 19, 2017
Messages
6
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

VBA Code:
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.
 
Solution

AnthonyA755

New Member
Joined
Apr 19, 2017
Messages
6
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

VBA Code:
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

New Member
Joined
Apr 19, 2017
Messages
6
Sorry, i sent my reply twice! Please disregard the second response (or if a mod could delete it, that would be great)
 

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
182
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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top