Relative Refs

[Darin Kramer]

Hi there,

Using VB, I want to tell Excel to got cell b10, which is easy enough
(Application.Goto Reference:="R10C2") ! but I then want to tell it to
move ONE cell down (One cell down is not B11 as its a filtered list so
next cell will change depending on results of filter...

Thanks!!!

Regards

Darin

*** Sent via Developersdex http://www.developersdex.com ***

 

[Peter T]

One way -

Sub test()
Dim i As Long
Dim rng As Range

For i = 10 To Rows.Count
If Not Rows(i).Hidden Then
Set rng = Cells(i, 2)
Exit For
End If
Next

If Not rng Is Nothing Then
Application.Goto rng
End If

End Sub

Regards,
Peter T

 

[Darin Kramer]

HI Peter,

Thanks - this takes me to the last row in the range, which is blank - I
want it to take me to the first row with data in it - which I know will
always be one line beneath row 10, column 2 (In prev eg I know I used
used b1, but its actually B10)....

Thanks!!!

Regards

D



*** Sent via Developersdex http://www.developersdex.com ***

 

[Peter T]

Hi Darren,

I'm confused, I thought you wanted to 'goto' the first cell including or
below B10 that is not in a (filtered) hidden row. In light testing that's
what the macro did.

which I know will
always be one line beneath row 10, column 2

so why not go directly to B11, guess I'm not following.

Regards,
Peter T

 

[Darin Kramer]

Works fine - my apologies Peter - I changed it to fit my situation but
changed it incorrectly! Now that Ive fixed my error, your piece of
brilliance works like a charm!

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***