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?

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

--
Gary''s Student - gsnu200815


"~L" wrote:

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?

Thank you for the help!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200815


"~L" wrote:

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?

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" wrote:

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?

Thanks, this seems to be doing the trick.

"Mike H" wrote:

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" wrote:

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?

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

"Mike H" wrote:

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" wrote:

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?

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

"~L" wrote:

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

"Mike H" wrote:

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" wrote:

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?

Apparently, I meant:

Worksheets("Data").Range("A2:A" & Lastrow).SpecialCells(xlFormulas,
xlNumbers).Select

but you put me on the right track again.

Thank you.

"Mike H" wrote:

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

"~L" wrote:

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

"Mike H" wrote:

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" wrote:

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?

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" wrote:

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?

Thank you for your reply.

"Michael" wrote:

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" wrote:

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?