Solved: Set range variable to cell above hidden rows

[mdmackillop]

Rows 6 to 10 are hidden. D11 is selected. What is the simplest way to set a range variable to the visible cell above? i.e. D5
Regards
MD

[mfegyver]

hi, not sure if i understood, but would it be :
Range("D5:D11").Select
Range("D11").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("D14").Select
ActiveSheet.Paste
Application.CutCopyMode = False

rgds
marcelo

[Bob Phillips]

Set rng = Rows("1:" & ActiveCell.Row - 1).SpecialCells(xlCellTypeVisible)
Set rng = Cells(rng(rng.Count).Row, ActiveCell.Column)

[malik641]

Man...I'm just not fast enough :mkay

[mdmackillop]

and if there are several blocks of hidden rows?

[malik641]

I was trying not to use loops...

EDIT: Missed something...
Public Sub test()
Dim rng As Excel.Range
Dim i As Long

Set rng = Selection

Do
i = i - 1
If rng.Offset(i + 1, rng.Column).Row = 1 Then
i = i + 1
Exit Do
End If
Loop While (rng.Offset(i, 0).EntireRow.Hidden = True)

rng.Offset(i, 0).Select
End Sub

[mdmackillop]

I was trying not to use loops...
As was I. It looks such a simple thing to do!

[malik641]

I know!!

I noticed Range.Previous, but this only simulates SHIFT+TAB which doesn't help us...and I don't believe the Help files because SHIFT+TAB doesn't select hidden columns, yet Range.Previous does.

[malik641]

We should make this a VBA challenge :)

[Bob Phillips]

Set rng = rows("1:" & ActiveCell.Row - 1).SpecialCells(xlCellTypeVisible)
ary = Split(Replace(rng.Address(0, 0), ":", ","), ",")
Set rng = Cells(Val(ary(UBound(ary))), ActiveCell.Column)

[mdmackillop]

:clap::clap::clap:
Thanks Bob. An ingenious approach.
Regards
Malcolm

[malik641]

Well done, Bob :clap:

You've won the VBA Challenge!! :ty: <--Bob

[mdmackillop]

Thanks all