Count Non Blank Rows
 

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

--
Message posted via http://www.officekb.com

Count Non Blank Rows
 

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

Hgth,
Merjet

Count Non Blank Rows
 

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

--
regards,
Tom Ogilvy


"Fredriksson via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

Count Non Blank Rows
 

try it this way:

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



--
Hope that helps.

Vergel Adriano


"Fredriksson via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

Count Non Blank Rows
 

Thanks I appreciate the help

Vergel Adriano wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Count Non Blank Rows
 

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"

Count Non Blank Rows
 

Thanks I appreciate the help
Tom Ogilvy wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Count Non Blank Rows
 

Try:

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

--
Jim
"Helmut Weber" wrote in message
...
| 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"
|
|
|

Count Non Blank Rows
 

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


"Fredriksson via OfficeKB.com" wrote:

Thanks I appreciate the help
Tom Ogilvy wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Count Non Blank Rows
 

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

--
Regards,
Tom Ogilvy



"Helmut Weber" wrote:

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"

Count Non Blank Rows
 

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


"Tom Ogilvy" wrote:

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


"Fredriksson via OfficeKB.com" wrote:

Thanks I appreciate the help
Tom Ogilvy wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Count Non Blank Rows
 

Thanks for all of your help

Jim Rech wrote:
Try:

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

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Count Non Blank Rows
 

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.

--
Regards,
Tom Ogilvy







"Jim Rech" wrote:

Try:

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

--
Jim
"Helmut Weber" wrote in message
...
| 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"
|
|
|