Count Non Blank Rows

[Fredriksson via OfficeKB.com]

I am trying to count the number of rows in a column that are non blank

Dim NumOfRows
Dim NumOfColumns
NumOfRows = CountA(Sheet1!$A:$A)
NumOfColumns = CountA(Sheet1!$2:$2)

the debugger keep erroring out at this line. Is there anouther function I
can use

 

[merjet]

NumOfRows = Application.CountA(Sheet1.Columns("$A:$A"))
NumOfColumns = Application.CountA(Sheet1.Rows("$2:$2"))

Hgth,
Merjet

 

[Tom Ogilvy]

Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA(Sheet1!$A:$A)
NumOfColumns = Application.CountA(Sheet1!$2:$2)

 

[Vergel Adriano]

try it this way:

With Worksheets("Sheet1")
NumOfRows = WorksheetFunction.CountA(.Range("A:A"))
NumOfColumns = WorksheetFunction.CountA(.Range("2:2"))
End With

 

[Fredriksson via OfficeKB.com]

Thanks I appreciate the help

try it this way:

With Worksheets("Sheet1")
NumOfRows = WorksheetFunction.CountA(.Range("A:A"))
NumOfColumns = WorksheetFunction.CountA(.Range("2:2"))
End With

I am trying to count the number of rows in a column that are non blank

[quoted text clipped - 5 lines]

the debugger keep erroring out at this line. Is there anouther function I
can use

 

[Helmut Weber]

Hi Tom,

Sub Test447()
Msgbox = Application.CountA(Sheet1!$A:$A)
End Sub

First I get "Invalid character" and the first $-sign is highlighted.
After switching languages (english-german, german-english),
to and fro, I get syntax-error.

Maybe it has to do with localization.
Though I'm using an US-version,
date and time settings interfere every now and then.
When and why, nobody knows.

How would Application.CountA(Sheet1!$A:$A) look
when using cells(row,column) instead of $A:$A.

Help for CountA tells me:
The page cannot be displayed.

I'm lost.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Fredriksson via OfficeKB.com]

Thanks I appreciate the help

Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA(Sheet1!$A:$A)
NumOfColumns = Application.CountA(Sheet1!$2:$2)

I am trying to count the number of rows in a column that are non blank

[quoted text clipped - 5 lines]

the debugger keep erroring out at this line. Is there anouther function I
can use

 

[Jim Rech]

Try:

Application.CountA(Range("Sheet1!$A:$A"))

--
Jim
| Hi Tom,
|
| Sub Test447()
| Msgbox = Application.CountA(Sheet1!$A:$A)
| End Sub
|
| First I get "Invalid character" and the first $-sign is highlighted.
| After switching languages (english-german, german-english),
| to and fro, I get syntax-error.
|
| Maybe it has to do with localization.
| Though I'm using an US-version,
| date and time settings interfere every now and then.
| When and why, nobody knows.
|
| How would Application.CountA(Sheet1!$A:$A) look
| when using cells(row,column) instead of $A:$A.
|
| Help for CountA tells me:
| The page cannot be displayed.
|
| I'm lost.
|
| --
| Greetings from Bavaria, Germany
|
| Helmut Weber, MVP WordVBA
|
| Win XP, Office 2003
| "red.sys" & Chr$(64) & "t-online.de"
|
|
|

 

[Tom Ogilvy]

whoops -
I need to make another adjustment


Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA([Sheet1!$A:$A])
NumOfColumns = Application.CountA([Sheet1!$2:$2])

0r

Dim NumOfRows
Dim NumOfColumns
With Worksheets("Sheet1")
NumOfRows = Application.CountA(.range("A:A"))
NumOfColumns = Application.CountA(.Range("2:2"))


--
Regards,
Tom Ogilvy

Thanks I appreciate the help

Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA(Sheet1!$A:$A)
NumOfColumns = Application.CountA(Sheet1!$2:$2)

I am trying to count the number of rows in a column that are non blank

[quoted text clipped - 5 lines]

the debugger keep erroring out at this line. Is there anouther function I
can use

 

[Tom Ogilvy]

Unfortunately, I just focused on the first error I came to - see my followup
posting for a more complete response.

 

[Tom Ogilvy]

of course the With would need to be paired with an End With

Dim NumOfRows
Dim NumOfColumns
With Worksheets("Sheet1")
NumOfRows = Application.CountA(.range("A:A"))
NumOfColumns = Application.CountA(.Range("2:2"))
End With

--
Regards,
Tom Ogilvy

whoops -
I need to make another adjustment


Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA([Sheet1!$A:$A])
NumOfColumns = Application.CountA([Sheet1!$2:$2])

0r

Dim NumOfRows
Dim NumOfColumns
With Worksheets("Sheet1")
NumOfRows = Application.CountA(.range("A:A"))
NumOfColumns = Application.CountA(.Range("2:2"))


--
Regards,
Tom Ogilvy

Thanks I appreciate the help

Dim NumOfRows
Dim NumOfColumns
NumOfRows = Application.CountA(Sheet1!$A:$A)
NumOfColumns = Application.CountA(Sheet1!$2:$2)

I am trying to count the number of rows in a column that are non blank

[quoted text clipped - 5 lines]
the debugger keep erroring out at this line. Is there anouther function I
can use

 

[Fredriksson via OfficeKB.com]

Thanks for all of your help

Try:

Application.CountA(Range("Sheet1!$A:$A"))

| Hi Tom,
|
[quoted text clipped - 18 lines]
|
| I'm lost.

 

[Tom Ogilvy]

Jim's addendum would be excellent in almost every context.

Just as added explanation for Helmut, in my subsequent response, I didn't
choose that particular approach because it would be problematic in a sheet
module other than sheet1. The unqualified Range would be equivalent to
me.range and raise an error since the argument refers to a different sheet.