Macro giving wrong result for counting rows

sathyaanil37

New Member
Joined
Sep 14, 2012
Messages
32
The below macro code is giving wrong count
Code:
<CODE>Dim rows As Singlerows = Sheet1.UsedRange.rows.Count - 1MsgBox (rows)</CODE></PRE><!-- END TEMPLATE: bbcode_code -->
sheet1 has the following data

empid name loc
1 aaaa ddd
2 bbbb ggg

4 cccc dddd


the 3rd row is empty in sheet1 but still it is showing number of rows as 4 instead of 3.I need macro it will not count if entire row is empty in that sheet and give correct count.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
This will give you how many rows in column A have data in

Code:
Sub test()
Dim x As Range
With Sheets("Sheet1")
    For Each x In .Range("A1:A" & .Cells(rows.Count, 1).End(xlUp).Row)
        If x <> "" Then i = i + 1
    Next x
MsgBox (i)
End With
End Sub
 

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
usedrange.rows.count give you the last populated row in the sheet. If you minus countblank("A:A") from that equation, it should work.
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
Oh you want it to look at the entire row. If the entire row is blank then do not count it?

try this:

Code:
Sub test()
Dim x As Range
With Sheets("Sheet1")
    For Each x In .Range("A1:A" & .Cells(rows.Count, 1).End(xlUp).Row)
        If Application.CountA(x.EntireRow) <> 0 Then i = i + 1
    Next x
MsgBox (i)
End With
End Sub
 

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540

ADVERTISEMENT

Another option and this may be quicker (not tested).
Code:
Sub test()
Dim lr As Long
Dim Ety As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Ety = Application.WorksheetFunction.CountBlank(Range("A1:A" & lr))
MsgBox (lr - Ety)
End Sub
 

sathyaanil37

New Member
Joined
Sep 14, 2012
Messages
32
For below data its not working.some times its giving correct and some times wrong count.
When column1 data null it is not counting that record.Only when entire row empty then it should not count that and remaing columns may have data also some times.This code counting column names row also but it should not count first row bcz it contains column names.
empiddeptid colid
123
123
23
1
23

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994

ADVERTISEMENT

Who are you replying to? We have both posted code.
 

sathyaanil37

New Member
Joined
Sep 14, 2012
Messages
32
Hi Dermie_72,
Your code is working fine.But
when my any row first column value as null it not counting that row.But it should count bcz some columns have null values also.
if entire row is null then only it can not count that record. and it is counting first row as well,But i dont want to count first row bcz it contains column names.
And i added one button in sheet1 and data added in sheet2.In button i added your code but when i click button it is showing count as 0.But it is not identifying data from sheet2.
 
Last edited:

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
This will stop it counting row 1 (the one with headers)

Code:
Sub test()
Dim x As Range
With Sheets("Sheet1")
    For Each x In .Range("A2:A" & .Cells(rows.Count, 1).End(xlUp).Row)
        If Application.CountA(x.EntireRow) <> 0 Then i = i + 1
    Next x
MsgBox (i)
End With
End Sub
 

sathyaanil37

New Member
Joined
Sep 14, 2012
Messages
32
Hi wrightyrx7,

Still if first column value coming as null in thzt row then it is not counting that record.

This will stop it counting row 1 (the one with headers)

Code:
Sub test()
Dim x As Range
With Sheets("Sheet1")
    For Each x In .Range("A2:A" & .Cells(rows.Count, 1).End(xlUp).Row)
        If Application.CountA(x.EntireRow) <> 0 Then i = i + 1
    Next x
MsgBox (i)
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,952
Messages
5,525,851
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top