Range syntax help for a VBA newbie

L

~L

I'm trying to make the line

For Each r In Worksheets("data").Range("A2:A25000")

not evaluate blank cells by counting the number of populated cells using

For Each r In Worksheets("data").Range("A2:A" &
WorksheetFunction.CountA(Data!A2:A25000))

But when I do that, I get runtime error 1004.

What is the correct way to phrase that?
 
G

Gary''s Student

Here is the "usual" way:

Sub missive()
n = Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Worksheets("data").Range("A2:A" & n)
' do something here
Next
End Sub
 
M

Mike H

Hi,

Do it this way

Lastrow = Worksheets("data").Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Worksheets("data").Range("A2:A" & Lastrow)

'do things

next

Mike
 
M

Michael

Try this:
myvar = WorksheetFunction.CountA(Sheets("Data").Range("A1:A25000"))
This will return the number of non blank cells in A1:A25000
Otherwise, you may want to consider re-evaluating your loop.
First set the range
set myrange=Worksheets("data").Range("A2:A25000")
'Then do this
For each r in myrange
if r = "" then
'do nothing
Else
'do something
End If
Next r
 
L

~L

Thanks, this seems to be doing the trick.

Mike H said:
Hi,

Do it this way

Lastrow = Worksheets("data").Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Worksheets("data").Range("A2:A" & Lastrow)

'do things

next

Mike
Click to expand...
 
L

~L

Thank you for the help!

Gary''s Student said:
Here is the "usual" way:

Sub missive()
n = Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Worksheets("data").Range("A2:A" & n)
' do something here
Next
End Sub
Click to expand...
 
L

~L

Thank you for your reply.

Michael said:
Try this:
myvar = WorksheetFunction.CountA(Sheets("Data").Range("A1:A25000"))
This will return the number of non blank cells in A1:A25000
Otherwise, you may want to consider re-evaluating your loop.
First set the range
set myrange=Worksheets("data").Range("A2:A25000")
'Then do this
For each r in myrange
if r = "" then
'do nothing
Else
'do something
End If
Next r


--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
Click to expand...
 
L

~L

Is there a way to modify this so that rows with formulas generating blanks
are not counted?
 
M

Mike H

Hi,

If you mean blanks in the middle of the range then maybe this

Sub versive()
Dim MyRange As Range
lastrow = Worksheets("data").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("data").Range("A2:A" & lastrow).SpecialCells(xlConstants).Select
Set MyRange = Selection

For Each r In MyRange
'do things
Next
End Sub

Mike
 
Top