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

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...
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> 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
>

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

> (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
news:#(E-Mail Removed)...
> 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
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>

First, both lines should start with "Set"

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

(E-Mail Removed) wrote:
>
> 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

--

Dave Peterson

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

(E-Mail Removed) wrote:

>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

> 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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> (E-Mail Removed) wrote:
>
> >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

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





(E-Mail Removed) 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(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
>
> (E-Mail Removed) wrote:
>
> >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

--

Dave Peterson