Row Count not working as expected

Question

I have a sub that does automated import of data from text delimited file (separator ‘^’) to excel sheet called "Report2" and then does further procedures on data within excel.

The following is an example of the structure of the text delimited file:

---

DEVICE: HOME// 

StartDate^1/1/11^EndDate^3/3/11

PtSSN^HbDate^HbValue^EsaD^EsaF^EsaS^EsaE^FeD^FeF

123456789^1/3/11^10.2^40MCG^Q1W^12/27/10^1/2/11^^

223456789^1/3/11^9.2^100MCG^Q1W^12/20/10^1/3/11^^

^^PtSSN2^LabDate^Ca^Phos^PTH^Alb

^^123456789^1/3/11^9.7^3.0^211.8^2.8

---

I need to:

1.       Find and confirm presence of the critical cell (header ptSSN)

2.       Delete all rows above this cell

3.       Counts non-blank cells below ptSSN to signify number of patients

The following is the piece of VBA code I have (working well except for the count):

---

Sub Procedure()

Dim Report2Sheet As Worksheet

Dim CountCellsColumn As Long

Dim cellPtSSNinReport2 As Range

Call ImportFromText2 'sub does import of data from text-delimitted file to 'Report2' sheet

Set Report2Sheet = Worksheets("Report2")

Report2Sheet.Activate

With Report2Sheet

'confirm presence of header ptSSN in Report2 sheet

Set cellPtSSNinReport2 = .Columns(1).Cells.Find(what:="PtSSN", LookIn:=xlValues, LookAt:=xlPart)

If cellPtSSNinReport2 Is Nothing Then

MsgBox "Header ptSSN Not found"

GoTo exithandler

End If

'Delete all rows above row that contains PtSSN Label in Report2sheet

If cellPtSSNinReport2.Row > 1 Then

.Range("1:" & (cellPtSSNinReport2.Row - 1)).Delete

End If

'Count nonempty cells in column A to get patient number

CountCellsColumn = Application.Count(Range("A:A"))

Debug.Print "CountCellsColumn  after delete = " & CountCellsColumn   

Debug.print "Number of patients  = " & CountCellsColumn -1  'note: first row is headers.

'........

end with

exithandler:

Exit Sub

---

 In the above sub, the following shows in the immediate window:

CountCellsColumn  after delete = 2

Number of patients  = 1

Rather than what I expect to be:

CountCellsColumn before delete = 3

Number of patients  = 2

I have confirmed that the data in Report2 sheet has PtSSN in Cell A1, and 2 patients in it at end of this procedure.

The count is the same even if I put the same count statement before the row deletion procedures.

what am I doing wrong with the count?

Thanks

Answer 1

Count only counts numeric values. CountA counts all cells with values.

You are deleting 1 for the header and that would not be included in Count because it is a string.

Answer 2

Thanks...This answers it.