Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Non Blank Rows
NumOfRows = Application.CountA(Sheet1.Columns("$A:$A"))
NumOfColumns = Application.CountA(Sheet1.Rows("$2:$2")) Hgth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" | | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count#cells w/= value in other column and not count blank c | Excel Worksheet Functions | |||
count how many rows contain nonblank in column A and blank in colu | Excel Discussion (Misc queries) | |||
How do I count non blank rows only? | New Users to Excel | |||
How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
Count Non-Blank Rows | Excel Worksheet Functions |