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

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

Hgth,
Merjet

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

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

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

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"

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

Try:

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

--
Jim
"Helmut Weber" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| 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"
|
|
|

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

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