counting rows counting empty rows

C

cparsons

I have the following code in my macro to help me count the rows in m
spreadsheet. The problem I am having is the cell it is looking at i
empty but has a formula in it. It appears that it is counting th
formulas too. How can I only count the cells that have data in them?


Code
-------------------
With Worksheets(Cur_Month).Range("EMP_HR_ID")
If .Offset(0, 0) = "" Or Null Then
MsgBox ("There were no HRIDs to process. Please Update the Information and Rerun the Application.")
HRID_cnt = 0
ElseIf .Offset(1, 0) = "" Or Null Then
HRID_cnt = 1
Else
HRID_cnt = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End If

End Wit
 
B

Bernie Deitrick

c,

Try

Else
HRID_cnt = Application.CountA(Range(.Offset(0, 0), .End(xlDown))) - _
Application.CountBlank(Range(.Offset(0, 0), .End(xlDown)))
End If

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
MACRO COLUMNS, SKIP ROWS 6
Help on Macro to hide empty rows 4
Can Vba workout if a selection is part of a group? 0
Command button to move completed action item 4
Date Stamp with protection 6
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents 3
MACRO PASTE CELL to COLUMN, SKIP ROWS 1

Top