Is there a limit to the number of items you can select?

[Guest]

I have the VBA code listed below.
I get an error Run-time error 1004
Method 'Range' of object '_Global' failed

Basicly what I want to do is select all rows with Column C = Independent

When I run it with lengthofselect = 79340 the error occurs on the
Range(RowNumHolderNew).Select statment

When I run it with lengthofselect = 16 it runs fine and selects the Rows I
want it to.

Is there a limit that you can only select so many rows at once in the
Range() function?

Do you have a simpler way of doing this?


i = 1

Do While Range("C" & i) <> ""

If Range("C" & i).Value = "Independent" Then
RowNumHolder = i & ":" & i & "," & RowNumHolder

End If
i = i + 1
Loop

lengthofselect = Len(RowNumHolder)
RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last
comma
Range(RowNumHolderNew).Select

 

[Guest]

Declare your variable as type long, iso as type integer: Dim LengthofSelect
as Long

 

[Guest]

That didn't seem to work.

Declare your variable as type long, iso as type integer: Dim LengthofSelect
as Long

--
Hth

Kassie Kasselman
Change xxx to hotmail

 

[Dave Peterson]

Dim Rng as range
dim i as long

i = 1
set rng = nothing
with activesheet
Do While trim(.Range("C" & i)) <> ""
If .Range("C" & i).Value = "Independent" Then
if rng is nothing then
set rng = .range("C" & i)
else
set rng = union(rng, .range("C" & i)
end if
end if
i = i + 1
Loop

if rng is nothing then
msgbox "Nothing to select"
else
rng.entirerow.select
end if

=========
Personally, I like
..cells(i,"C")
instead of
..range("C" & i)

But both will work ok.

 

[Dave Peterson]

Oops. Add one more closing paren:

set rng = union(rng, .range("C" & i)
becomes
set rng = union(rng, .range("C" & i))

 

[Guest]

Works great,
Could you explain it a bit using the Unions?

Oops. Add one more closing paren:

set rng = union(rng, .range("C" & i)
becomes
set rng = union(rng, .range("C" & i))

 

[Dave Peterson]

Union() with ranges is like using concatenate() with strings--or sum() with
numbers.

It forms a giant(?) range of cells.

try:

with activesheet
union(.range("a1"),.range("b3"),.range("c5")).select
end with

Don't use this in real life, though:
activesheet.range("a1,b3,c5").select
would be easier to understand.

Works great,
Could you explain it a bit using the Unions?