asked on

How can I get the number of rows with text from an Excel document (and remove blank rows)

I have 3 Excel documents and I have to get the number of rows in each one, the number or rows that are NOT blank, but the problem is there are a lot of blank rows in between the rows with text, and right now my code counts these rows as if they weren't blank. Plus the spreadsheet doesn't look good like this...with all these blank rows all over the place...

This is my code but it doesn't work. The first one the CoCount variable returns the correct value, but the other two do NOT.
This is because the first one is getting the value of the original Excel spreadsheet (oSheet) where there are no blank rows in between...

The other two show the rows containing errors (oSheet_Error) or duplicates ( oSheet_Dup) from the original.
But if the computer finds an error-row at line 12 in the original, it places it at line 12 in the error file leaving the first 11 rows blank...I need to find a way to get rid of these blank rows...Maybe after that my code will work...?!?

Dim CoCount As Integer
Dim CoErrorCount As Integer
Dim CoDupCount As Integer

CoCount = oSheet.UsedRange.Rows.count - 1
CoErrorCount = oSheet_Error.UsedRange.Rows.count - 1
CoDupCount = oSheet_Dup.UsedRange.Rows.count - 1

MsgBox (CoCount)

ASKER CERTIFIED SOLUTION

fds_fatboy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbzhhu

Here is the code you need , I believe

Sub test()
MsgBox GetCountOfRowsWithData(ActiveWorkbook.Sheets(1))
End Sub

Function GetCountOfRowsWithData(oSheet As Worksheet) As Integer
Dim iCount As Integer
Dim iRows As Integer
Dim MyRange As Range

Set MyRange = oSheet.UsedRange

For iRows = 1 To oSheet.UsedRange.Rows.Count
If IsNull(MyRange.Rows(iRows).EntireRow.Text) Then
iCount = iCount + 1
Else
'Blank row!!!!!
End If
Next iRows

GetCountOfRowsWithData = iCount
End Function

gbzhhu

If you want to remove the blank rows then do this at the point I marked 'Blank row!!!!! using

MyRange.Rows(iRows).EntireRow.Delete