Best position in VBA code line for
 

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

Best position in VBA code line for
 

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

myRange = wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e).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...
--
HTH...

Jim Thomlinson


" 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

Best position in VBA code line for
 

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(xlCellTypeVisibl e)
ie restricts the search to the first column of your source range

wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e).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


wrote in message
...
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

Best position in VBA code line for
 

(probably unlikely looking for visible cells)

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

Peter T

"Peter T" <peter_t@discussions wrote in message
...
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(xlCellTypeVisibl e)
ie restricts the search to the first column of your source range

wks.Range("A2:F100").SpecialCells(xlCellTypeVisibl e).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


wrote in message
...
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

Best position in VBA code line for
 

First, both lines should start with "Set"

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

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

Best position in VBA code line for
 

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

Best position in VBA code line for
 

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


wrote in message
...
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

Best position in VBA code line for
 

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