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" wrote in message
oups.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" wrote in message
oups.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" wrote in message
oups.com...
WOW! Thanks guys! This is exactly what I was looking for...It worked
great!!