VBA Range Issue : Cannot programatically select more than 42 columns

[Learner]

I have a program that loops thru each rows in a worksheet and have to
highlight some rows based on a condition.
Following is the Algorithm

strMultipleRow = ""
Do While Cells(i, 1) <> ""
If ValidEntry(Cells(i,1).value) = False Then
strMultipleRow = strMultipleRow & i & ":" & i & ","
End If
i = i + 1
Loop


strMultipleRow = Left(strMultipleRow, Len(strMultipleRow) - 1)

Range(strMultipleRow).Select


Everything works fine, I have only less than 42 rows to be selcted
(highlighted). If the number of rows to be selected goes above 43 it
returns an runtime error 404 : Method 'Range' of object Global failed.

I want to find out whether any limitation on the number of rows that
VBA can programatically highlight. What is the workaround ?

Someone suggested to prequalify the selection with "xlSheet". I tried
it and got the same behaviour. Works fine if it has less than 42 but
fails if greater than 43.

GURUS PLEASE HELP...

 

[Bob Phillips]

I guess that the string is exceeding 255 characters. Try this instead

Dim rng As Range
strMultipleRow = ""
i = 1
Do While Cells(i, 1) <> ""
If ValidEntry(Cells(i, 1).Value) = False Then
If rng Is Nothing Then
Set rng = Cells(i, 1)
Else
Set rng = Union(rng, Cells(i, 1))

End If
i = i + 1
Loop

If Not rng Is Nothing Then
rng.EntireRow.Select
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

It is a limitation of the string size you are building. Better is

Dim rng as Range

i = 1
Do While Cells(i, 1) <> ""
If ValidEntry(Cells(i,1).value) = False Then
if rng is nothing then
set rng = cells(i,1)
else
set rng = union(rng,cells(i,1))
end if
End if
i = i + 1
Loop
if not rng is nothing then
rng.EntireRow.Select
End if


That will get you up to 8192 areas (not just rows).

 

[Learner]

WOW! Thanks guys! This is exactly what I was looking for...It worked
great!!

 

[Bob Phillips]

So good, so good, you got it twice

Bob