Maybe some more testing would be good.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myRng As Range

Set wks = Worksheets.Add

With wks
.Range("b1,d1").EntireRow.Hidden = True
.Rows(12).Resize(24).Hidden = True
Set myRng = wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e) _
.Resize(, 1)
End With
End Sub





wrote:

Thanks, Dave, I did forget to include "Set" in my example but OK in code.

Jim & Peter, my choice was wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e).Resize(, 1)
as it logically went from largest to smallest data sets.

It is not often that qualifers/modifiers can be placed in more than one location in VBA.

Thanks EagleOne

wrote:

2003/2007

Both lines do not fail:

myRange = wks.Range("A2:F100").Resize(, 1).SpecialCells(xlCellTypeVisible)

-OR-

myRange = wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e).Resize(, 1)

Which is best and a little bit as to why

TIA EagleOne

--

Dave Peterson