VBA Range Issue : Cannot programatically select more than 42 columns

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

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)


"Learner" <tomjcb@gmail.com> wrote in message
news:1107203453.618669.203020@z14g2000cwz.googlegroups.com...
> 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...
>

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).
--
Regards,
Tom Ogilvy

"Learner" <tomjcb@gmail.com> wrote in message
news:1107203453.618669.203020@z14g2000cwz.googlegroups.com...
> 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...
>

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

So good, so good, you got it twice :-)

Bob


"Learner" <tomjcb@gmail.com> wrote in message
news:1107207612.944652.191620@z14g2000cwz.googlegroups.com...
> WOW! Thanks guys! This is exactly what I was looking for...It worked
> great!!
>