Range syntax help for a VBA newbie

[~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?

 

[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

 

[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

 

[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]

Thanks, this seems to be doing the trick.

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

 

[~L]

Thank you for the help!

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

 

[~L]

Thank you for your reply.

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.

 

[~L]

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

 

[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