Macro giving wrong result for counting rows

[sathyaanil37]

The below macro code is giving wrong count

<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.

 

[dermie_72]

Try

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

If the last row of A was blank it was being ignored with the rows.count, 1 code. Using the usedrange.rows.count it gets all rows in the sheet.
Hope that helps.

BTW. In you first example, both codes provided would have worked. The closer the example is to the actual data, the better we can assist you. Just saying is all.

 

[sathyaanil37]

Thx dermie_72.Your code is working fine.is there any option to print that row count value in another sheet instead of msgbox.

 

[wrightyrx7]

You mean put the value in the cell of another sheet?

If so, what's the sheet name and which cell?

 

[sathyaanil37]

Hi Wrighty,
Sheet2 and b2 cell

 

[wrightyrx7]

Try this

Sub test()
Dim x As Range
With Sheets("Sheet1")
    For Each x In .Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
        If Application.CountA(x.EntireRow) <> 0 Then i = i + 1
    Next x
Sheets("Sheet2").Range("B2").Value = i
End With
End Sub

 

[dermie_72]

Just as I thought Wrightyrx7, they got what they needed, then nicked off without even a thanks for all of your help. I liked your coding mate and it considered most variations in the data, whereas mine only looked at A. (like the initial request). So while mine was right, ours was more versatile. Nice work.

 

[wrightyrx7]

Just as I thought Wrightyrx7, they got what they needed, then nicked off without even a thanks for all of your help.

Hi Dermie,

I know what you mean, some people dont appreciate the time we take to help them out. I just avoid helping people like that in the future.

I liked your coding mate and it considered most variations in the data, whereas mine only looked at A. (like the initial request). So while mine was right, ours was more versatile. Nice work.

I just try and keep the amount of data to be checked in a loop to a minimum so the code can run a little quicker.

 

[sathyaanil37]

Thanks Dermie and Wrightyrx7 for the help.