Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of rows/columns occupied
Is there a function which counts the number of
rows / columns in an Excel worksheet which contain something (i.e. are not empty)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of rows/columns occupied
=CountA(A:A)
=CountA(1:1) "Will" wrote: Is there a function which counts the number of rows / columns in an Excel worksheet which contain something (i.e. are not empty)? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of rows/columns occupied
Hi,
Alt+F11 to open vb editor, right click 'This Workbook' and insert module and paste these 2 UDF's below in call with =usedrows() =usedcolumns() Function UsedRows() As Long UsedRows = ActiveSheet.UsedRange.Rows.Count End Function Function UsedColumns() As Long UsedColumns = ActiveSheet.UsedRange.Columns.Count End Function Mike "Will" wrote: Is there a function which counts the number of rows / columns in an Excel worksheet which contain something (i.e. are not empty)? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of rows/columns occupied
Those functions will count blank rows and/or blank columns that are located
within the UserRange (plus it is possible for the UsedRange to contain blank rows and/or blank columns after the last piece of data in a row or column). -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, Alt+F11 to open vb editor, right click 'This Workbook' and insert module and paste these 2 UDF's below in call with =usedrows() =usedcolumns() Function UsedRows() As Long UsedRows = ActiveSheet.UsedRange.Rows.Count End Function Function UsedColumns() As Long UsedColumns = ActiveSheet.UsedRange.Columns.Count End Function Mike "Will" wrote: Is there a function which counts the number of rows / columns in an Excel worksheet which contain something (i.e. are not empty)? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of rows/columns occupied
Hi,
As stated the answer is no. You have asked for A function which counts all the occupied rows and columns in a worksheet. There is no function that will do both at once, rows and columns. Also, there is no built in function which will calculate the number of occupied rows in all columns at once. What you could do is insert a new column A and a new row in position 1, enter the formula =COUNTA(B2:IV2) in A2 and copy it down as far as you want 65536 or 1,048,576 (2007). Then in A1 enter the formula =SUM(A2:A65536) This will count the number of rows that have anything on them. You can do something similar for columns. Also, none of these formulas consider whether the cells are formatted if that is important. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Will" wrote: Is there a function which counts the number of rows / columns in an Excel worksheet which contain something (i.e. are not empty)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
limited columns and rows number | New Users to Excel | |||
Customize number of rows and columns | Excel Discussion (Misc queries) | |||
Set number of columns/rows? | Excel Discussion (Misc queries) | |||
Maximun number of rows and columns | Excel Discussion (Misc queries) | |||
number of columns/rows | Excel Discussion (Misc queries) |