Best position in VBA code line for

[EagleOne]

2003/2007

Both lines do not fail:

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

-OR-

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

Which is best and a little bit as to why

TIA EagleOne

 

[Jim Thomlinson]

Purely as a guess (I have not benchmarked which is more efficient if at all)
I would say

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

would be more efficient. Specials cells will have more overhead associated
with it than resize. That being the case I want to execute the specialcells
method on the smaller range and then expand the range at the end. But that is
just idle speculation...

 

[Peter T]

They are radically different, so which is best depends on what you want. BTW
both might 'correctly' fail if no cells were found in the range (visible
cells), so you always need an error handler with SpecialCells.

wks.Range("A2:F100").Resize(, 1).SpecialCells(xlCellTypeVisible)
could be re-written as
wks.Range("A2:A100").SpecialCells(xlCellTypeVisible)
ie restricts the search to the first column of your source range

wks.Range("A2:F100").SpecialCells(xlCellTypeVisible).Resize(, 1)
- returns the first column of the 'found cells' range, whatever that might
be, and quite likely to give a misleading range if multiple areas are
returned (probably unlikely looking for visible cells)

Regards,
Peter T

 

[Peter T]

(probably unlikely looking for visible cells)

wouldn't fail if only columns are hidden but would fail if rows are hidden

Peter T

 

[Dave Peterson]

First, both lines should start with "Set"

Second, the bottom one could fail depending on where those hidden cells are.

 

[EagleOne]

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(xlCellTypeVisible).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

 

[Peter T]

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

location in VBA.

But that doesn't make them the same at all, ie the code to do or to return
same.

That particularly applies to the two lines of code you posted, I'm not sure
if you read what I tried to explain earlier.

Unless you have total control over your sheet, and/or good error handling,
your choice of code looks prone to problems.

Regards,
Peter T

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(xlCellTypeVisible).Resize(, 1)

 

[Dave Peterson]

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(xlCellTypeVisible) _
.Resize(, 1)
End With
End Sub