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.

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

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

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