VBA: using the output number a defined cell

[kegfood]

I'm trying to unhide rows one at a time using VBA, I have found some code that will do this, but the starting row that I want to unhide changes and I want stop unhiding at a dyanmic row as well.

The code I've been using is below, and I'm trying to modify "a1" and "a100" to reference two named/defined cells in my workbook that dynamically tells me the start and end rows, but I don't know how to incorporate them?

Dim myr As Range
For Each myr In Range("a1", "a100")
If Rows(myr.Row).Hidden = True Then
Rows(myr.Row).Hidden = False
Exit Sub
End If
Next myr
End Sub

I pretty much a beginner on VBA, so apologies if something here doesn't make sense.
Any thoughts welcomed!
cheers

 

[Gary McMaster]

If you have named a starting cell and an ending cell you can just use the names in the code.

Gary

For Each myr In Range("StartCellName", "EndCellName")

 

[kegfood]

Re: VBA: using the output number from a defined cell

Thanks for the speedy reponse...
I have tried this and for some reason, nothing happens at all. I was wondering if it is becuase it's using the cell reference for the defined cell (eg. "startcellname" is cell B170) rather than the cell result (which at the moment is 59)?
cheers,
kev

 

[Gary McMaster]

It sounds like you may want something like the statement below:

For Each myr In Range("A" & ActiveSheet.Range("startcellname").Value & ":" & "A" & ActiveSheet.Range("endcellname").Value)

Gary

 

[JackDanIce]

Trying to understand your code, you're looping between a starting and ending cell, but as soon as you encounter a hidden row, you want to unhide it and then stop the macro?

Rows(myr.Row).Hidden = False
Exit Sub

What if there's a row hidden further along?

 

[JackDanIce]

Think I understand your problem, are you using named ranges in your worksheet? If so and assuming you do want to unhide all the hidden rows in your defined range, this code works for me and is faster than looping:

Sub UnHideDefinedRowsInRange()
Range(Range("startcellname").Address, Range("endcellname").Address).Rows.Hidden = False
End Sub

 

[kegfood]

Thanks guys...
Not quite there yet though. Gary, for some reason when I use your code, the macro just seem to do anything (I know tricky when you can't see it!). I think I understand what you are trying to do, just I'm not too familiar with the commands to do it (in an excel formula it would be easy to create a cell ref in this way).

JackDanIce, the idea is to unhide the rows one at a time, ie. with each click of the macro button and stop working at a specific row. The problem being the start and end row for the macro changes within my worksheet...

Cheers,
kev

 

[JackDanIce]

Is this what you want? Try:

Sub UnHideMyRows()
Dim StopLooping, i As Long
On Error Resume Next
i = InputBox("What is the number of your starting row?")
If i = 0 Then
    MsgBox "Invalid starting row entered, please re-start macro"
    Exit Sub
End If
Do While StopLooping <> vbYes
   If Range("A" & i).Rows.Hidden = True Then
      Range("A" & i).Rows.Hidden = False
      StopLooping = MsgBox("Do you wish to stop?", vbYesNo)
      If StopLooping = vbYes Then: Exit Sub
   End If
   i = i + 1
   If IsEmpty(Range("A" & i)) Then StopLooping = vbYes
Loop
End Sub

 

[kegfood]

Apologies Gary, user error when adding your code in!!... that seems to work in the tests so far. Thanks very much, I'll do my best to understand it and learn from this one!
Cheers for the help guys...

 

[Gary McMaster]

It just concatenates "A" + value in start cell + ":" + A + value in end cell as a string.

You can see the result with this (change cell names to match):

MsgBox "a" & ActiveSheet.Range("startcellname").Value & ":" & "a" & ActiveSheet.Range("endcellname").Value

Glad you got it working.

Gary