PDA

View Full Version : Solved: Set range variable to cell above hidden rows



mdmackillop
11-01-2007, 10:00 AM
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
11-01-2007, 10:08 AM
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
11-01-2007, 10:12 AM
Set rng = Rows("1:" & ActiveCell.Row - 1).SpecialCells(xlCellTypeVisible)
Set rng = Cells(rng(rng.Count).Row, ActiveCell.Column)

malik641
11-01-2007, 10:25 AM
Man...I'm just not fast enough :mkay

mdmackillop
11-01-2007, 11:48 AM
and if there are several blocks of hidden rows?

malik641
11-01-2007, 12:14 PM
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
11-01-2007, 12:17 PM
I was trying not to use loops...
As was I. It looks such a simple thing to do!

malik641
11-01-2007, 12:26 PM
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
11-01-2007, 12:27 PM
We should make this a VBA challenge :)

Bob Phillips
11-01-2007, 01:50 PM
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
11-01-2007, 02:33 PM
:clap::clap::clap:
Thanks Bob. An ingenious approach.
Regards
Malcolm

malik641
11-01-2007, 05:57 PM
Well done, Bob :clap:

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

mdmackillop
11-02-2007, 05:54 AM
Thanks all