Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?

Range syntax help for a VBA newbie
 

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?