VBA help on row count in if/then statement

[ItalianPlatinum]

Hello - my current vba will evaluate if column H is > 2 if it is then it proceeds if not then it will stop. For some reason if i select the cell H (myself) the count is 2

but my VBA is evaluating something greater than 2. Am i using the wrong code for this?

If WsDIST.Range("H" & rows.count).End(xlUp).row > 2 Then

 

[Joe4]

would that be the most efficient way to accomplish what I am doing? in your expert opinion

It is pretty quick and simple. It is how I would do it.

 

[ItalianPlatinum]

ok thanks something like this you think suffices?

If WsDIST.Application.WorksheetFunction.CountA("H:H") > 1 Then

 

[Joe4]

The way VBA uses ranges is a little different (as you have written it, it sees "H:H" as literal text, not a range).
I usually build the range as a range variable, and then use it like this:

    Dim rng As Range
    Set rng = WsDIST.Range("H:H")
   
    If Application.WorksheetFunction.CountA(rng) > 1 Then

Note if you want to know if there are at least 2 records, then you will need to check for something like:

>3

or

>=3

(2 records plus 1 header record)

 

[Micron]

application.worksheetfunction.CountA(sheets("Sheet2").range("H:H"))
works fine for me (at least in immediate window)?

 

[Joe4]

application.worksheetfunction.CountA(sheets("Sheet2").range("H:H"))
works fine for me (at least in immediate window)?

Yep, that should work too, putting it all explicitly between the quotes, as long as you have the complete sheet and range references.
I just like the cleaner look of the using variable, but that's just me.