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

 

[Micron]

Not sure what you're asking. That returns the row number of the last value in column H, which would just happen to be the row count if you include all rows (even blank cells in H).

 

[ItalianPlatinum]

Okay that may explain it - I am looking for something to count the used data in column H which in my case is really 2.

 

[Micron]

So this should be 2 and not 9?

Then you just need to count non blank cells?

 

[Joe4]

You can just use the COUNTA function in Excel to count the number of non-blank cells in a range.

You can use COUNTA in VBA by using Application.WorksheetFunction.CountA(...)
See here: The COUNTA Function - Use It In Your Own VBA Code! - Acuity Training.

 

[ItalianPlatinum]

So this should be 2 and not 9?
View attachment 88558
Then you just need to count non blank cells?

Yea well technically 3. but yes 2 would be preferred to ignore the header. via VBA would be desired.

 

[Joe4]

Yea well technically 3. but yes 2 would be preferred to ignore the header. via VBA would be desired.

Take a look at the link I provided. Fairly simple to get the count using COUNTA, even in VBA.

 

[Micron]

Yea well technically 3

Technically 2 for what I posted. There is no header in what I showed; that is a column label.
I was going to say use CountA if I was on the right track but too late I guess. However, you will still need to find the last row with a value in H and then set the range accordingly to eliminate header row(s).

 

[Joe4]

However, you will still need to find the last row with a value in H and then set the range accordingly to eliminate header row(s).

Or take the lazy way out and just use COUNTA on the whole column, and then subtract 1 for the header!

 

[ItalianPlatinum]

Take a look at the link I provided. Fairly simple to get the count using COUNTA, even in VBA.

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