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
!--c1-->
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.