Need a check on Columns and cells for empty/null cells  past a column header

Fordraiders
Fordraiders used Ask the Experts™
on
Need a check on cells for empty/null cells  past a column header

vba excel 2010

I have this code trying to count my rows:  The data will either be in Column 1(a)   Or Column 2(b)
While Len(.Cells(lastRow, 1)) > 0 Or Len(.Cells(lastRow, 2)) > 0
                lastRow = lastRow + 1
Wend
The column Header are always in rows 3

Sometimes in Column1 the cells will be empty and i need to calculate the rows in Column2
Both Column cells will never be both filled in:

Will always look like this:
Column1            Column2
account              track       <----   row 3
11111
22321
445436

Or
Column1            Column2
account              track           <----   row 3
                            32de4
                            656768yu
                           65tgty

The code above seems ok(working), just wanting to make sure

Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

Instead of Loop you may use below code:
Sub CountNonBlankCells()
Dim Ws As Worksheet
Dim LRow As Long
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
LRow = Ws.UsedRange.Rows(Ws.UsedRange.Rows.count).Row
Application.ScreenUpdating = False
Ws.Range("C2").Value = WorksheetFunction.CountA(Ws.Range("A3:B" & LRow))
Application.ScreenUpdating = True
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
Why not use a formula? COUNTBLANK returns a count of empty cells in a range

=COUNTBLANK(Range)

COUNTBLANK function

The above would be dynamic if you used a Table with a Total Row
Fordraiders

Author

Commented:
THANKS

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial