Hi,

I have some code to hide/show rows when a command button is clicked e.g.

Private Sub CommandButton3_Click()
Rows ("58:123") Select
Selection.EntireRow.Hidden = False
Range("B55").Select
End Sub

I would like to replace the "58:123" with a dynamic named range. The idea is that the users can add/delete records within the dynamic range and the code will still hide/show the correct records. I have used named ranges but I cannot get the syntax correct (if it's possible?) to replace the "58:123". Any suggestions?

Thanks in advance

Hello and welcome to UA!

You can add this to a command button to toggle the hidden property for a range. This code assumes that your data is in column A. Change the column reference to whichever column will have the bottommost data.



HTH,

Welcome to Utter Access

... and another method...

you do not need to select the rows -- in fact, it will run faster if you don't

Hi,

Thanks for the welcomes and the suggestions.


I'm not sure how Range("A65536").End(xlUp)) works exactly(guessing it reads backwards from A65536 ?) but part of the problem is I have several ranges of values in the same column. The only thing I can say about the ranges is that they have continuous non zero values and known starting cells. i.e. ranges are separated by at least one empty cell.

So I have put together your suggestions to get

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

which if fine when rows are added/deleted within the "test" named range but fails when cells are added on at the end - any thoughts?

Thanks again!

p.s Crystal - how does the format(mRow1, "0") work ? - it's not just for quoting the variables is it?

OK, we can modify my first example.




HTH,

this



is the same as this:

Rows("58:123").EntireRow.Hidden = False

naturally, you could use logic to set the values for the row variables

this is what you need to do --

paste the code you have been given by everyone into an Excel module sheet

everytime you do not understand a keyword, press F1 and get help for that command. Print the help as you look it up and start making yourself a notebook.

after you have looked up the commands, decide how YOU want to code your routine

Thanks for all the help - this code is just what I need.

you're welcome