Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get count of active (non-empty) rows and columns
Hi, it is possible to get count of non-empty rows and columns in worksheet? I
cant find any property in Excel.Worksheet. Reading all cells and checking value not null or empty very slow. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get count of active (non-empty) rows and columns
Each worksheet has a .usedrange property.
But that doesn't mean that it accurately represents the non-empty rows and columns. If you put a value in E5 and another value in L20, you would see 16 rows and 8 columns with something like: With ActiveSheet.UsedRange MsgBox .Address & vbLf & .Rows.Count & vbLf & .Columns.Count End With Even though only 2 rows and two columns are non-empty. And if you use a cell and then clear that cell, excel doesn't automatically readjust that usedrange to what you would want. Debra Dalgleish does share some techniques for resetting that usedrange: http://contextures.com/xlfaqApp.html#Unused If you really wanted to know the number of non-empty rows and non-empty columns... Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngBoth As Range With ActiveSheet.UsedRange Set RngConst = Nothing Set RngForm = Nothing On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) On Error Resume Next If RngConst Is Nothing Then Set RngBoth = RngForm ElseIf RngForm Is Nothing Then Set RngBoth = RngConst Else Set RngBoth = Union(RngConst, RngForm) End If If RngBoth Is Nothing Then MsgBox "no used rows or columns!" Else MsgBox "Rows: " & Intersect(RngBoth.EntireRow, _ .Columns(1)).Cells.Count _ & vbLf & _ "Cols: " & Intersect(RngBoth.EntireColumn, .Rows(1)).Cells.Count End If End With End Sub baga wrote: Hi, it is possible to get count of non-empty rows and columns in worksheet? I cant find any property in Excel.Worksheet. Reading all cells and checking value not null or empty very slow. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get count of active (non-empty) rows and columns
Thanks for quick response!
"Dave Peterson" wrote: Each worksheet has a .usedrange property. But that doesn't mean that it accurately represents the non-empty rows and columns. If you put a value in E5 and another value in L20, you would see 16 rows and 8 columns with something like: With ActiveSheet.UsedRange MsgBox .Address & vbLf & .Rows.Count & vbLf & .Columns.Count End With Even though only 2 rows and two columns are non-empty. And if you use a cell and then clear that cell, excel doesn't automatically readjust that usedrange to what you would want. Debra Dalgleish does share some techniques for resetting that usedrange: http://contextures.com/xlfaqApp.html#Unused If you really wanted to know the number of non-empty rows and non-empty columns... Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngBoth As Range With ActiveSheet.UsedRange Set RngConst = Nothing Set RngForm = Nothing On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) On Error Resume Next If RngConst Is Nothing Then Set RngBoth = RngForm ElseIf RngForm Is Nothing Then Set RngBoth = RngConst Else Set RngBoth = Union(RngConst, RngForm) End If If RngBoth Is Nothing Then MsgBox "no used rows or columns!" Else MsgBox "Rows: " & Intersect(RngBoth.EntireRow, _ .Columns(1)).Cells.Count _ & vbLf & _ "Cols: " & Intersect(RngBoth.EntireColumn, .Rows(1)).Cells.Count End If End With End Sub baga wrote: Hi, it is possible to get count of non-empty rows and columns in worksheet? I cant find any property in Excel.Worksheet. Reading all cells and checking value not null or empty very slow. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get count of active (non-empty) rows and columns
Hi,
I want to achieve same thing in C#. I need to read the actual used range of excel sheet. in C#, there is no functions like Union() and Intersect(). So, pl help me to achieve same thing in C#. Regards. "Dave Peterson" wrote: Each worksheet has a .usedrange property. But that doesn't mean that it accurately represents the non-empty rows and columns. If you put a value in E5 and another value in L20, you would see 16 rows and 8 columns with something like: With ActiveSheet.UsedRange MsgBox .Address & vbLf & .Rows.Count & vbLf & .Columns.Count End With Even though only 2 rows and two columns are non-empty. And if you use a cell and then clear that cell, excel doesn't automatically readjust that usedrange to what you would want. Debra Dalgleish does share some techniques for resetting that usedrange: http://contextures.com/xlfaqApp.html#Unused If you really wanted to know the number of non-empty rows and non-empty columns... Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngBoth As Range With ActiveSheet.UsedRange Set RngConst = Nothing Set RngForm = Nothing On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) On Error Resume Next If RngConst Is Nothing Then Set RngBoth = RngForm ElseIf RngForm Is Nothing Then Set RngBoth = RngConst Else Set RngBoth = Union(RngConst, RngForm) End If If RngBoth Is Nothing Then MsgBox "no used rows or columns!" Else MsgBox "Rows: " & Intersect(RngBoth.EntireRow, _ .Columns(1)).Cells.Count _ & vbLf & _ "Cols: " & Intersect(RngBoth.EntireColumn, .Rows(1)).Cells.Count End If End With End Sub baga wrote: Hi, it is possible to get count of non-empty rows and columns in worksheet? I cant find any property in Excel.Worksheet. Reading all cells and checking value not null or empty very slow. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of columns on row that are not empty within a ran | Excel Discussion (Misc queries) | |||
Empty rows count | Excel Worksheet Functions | |||
count number of non empty columns | Excel Programming | |||
count non-empty rows | Excel Programming | |||
Count almost empty rows | Excel Programming |