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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
usedrange.rows.count give you the last populated row in the sheet. If you minus countblank("A:A") from that equation, it should work.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top