VBA Macro Dynamically update row references

[Tashat]

Hello all

I have a VBA macro assigned to buttons to hide and unhide rows. However, if I insert or delete rows anywhere on the worksheet, the row references remain the same, which means the macro hides the wrong rows. Is there a way to amend the macro so that if I insert a row, it updates the row range to 68:170 for example? Many thanks in advance.

Sub unHide_Rows_org()

'To unHide Rows 67 to 169
Rows("67:169").EntireRow.Hidden = False

End Sub
Sub Hide_Rows_org()

'To Hide Rows 67 to 169
Rows("67:169").EntireRow.Hidden = True

End Sub

 

[cmowla]

Do you have anything typed in your cells? Because if you type some text after the "divide" between the top half of visible rows and the bottom half (where the halves are obviously not an equal amount of rows, but . . . ), you should find that the cells containing those text move down if you insert new rows above that text or move up if you delete rows above that text.

Another way to look at it is that if you subtract the row number above the divide from the row number below the divide and get a number, if you then delete or insert new rows about the divide, the difference between those two rows are the same. (Which means that you did successfully hide x number of rows.)

(In short, your code to hide/unhide is correct.)

 

[Tashat]

Thanks cmowla. I think what I need is some changes to my code so that it allows me to add / remove rows and it automatically adjusts the row references of what is hidden / unhidden. I'm new to macros, so I don't understand the syntax.

 

[Fluff]

You could use a named range, that way your code wouldn't need changing. If you select rows 67:169 & then give them a name (say HideRows) you could then use

Range("HideRows").EntireRow.Hidden = True

 

[Tashat]

You could use a named range, that way your code wouldn't need changing. If you select rows 67:169 & then give them a name (say HideRows) you could then use

Range("HideRows").EntireRow.Hidden = True

Perfect solution. thank you. I wondered about using a named range, but wasn't sure of the syntax / code. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.