Full Version: Can I use a dynamic range in Rows(*).Select
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
jd1
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
Luceze
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.

CODE
    With Range("A58", Range("A65536").End(xlUp)).EntireRow
        If .Hidden = True Then
            .Hidden = False
            Else
            .Hidden = True
        End If
    End With


HTH,
strive4peace
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


CODE
   Dim mRow1 As Long, mRow2 As Long
   mRow1 = 58
   mRow2 = 123
   Rows(Format(mRow1, "0") & ":" & Format(mRow2, "0")).EntireRow.Hidden = False
jd1
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?
Luceze
OK, we can modify my first example.


CODE
    With Range("A58", Range("A58").End(xlDown)).EntireRow
        If .Hidden = True Then
            .Hidden = False
            Else
            .Hidden = True
        End If
    End With


HTH,
strive4peace
this

CODE
   Dim mRow1 As Long, mRow2 As Long
   mRow1 = 58
   mRow2 = 123
   Rows(Format(mRow1, "0") & ":" & Format(mRow2, "0")).EntireRow.Hidden = False


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
jd1
Thanks for all the help - this code is just what I need.
strive4peace
you're welcome wink.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.