COUNT within a range

[Carl Clements]

Hi,

Is there some code that counts the number of columns and rows in a range of cells? I want the macro to count until it finds an empty cell. I assume the COUNTA function will be involved.

The worksheet contains data from A26 to J34 and I want the formula to ignore rows above A26.

Thanks,
Carl

 

[villy]

Hope you find it useful, this works fine with me..

Sub count()
cnt = 0
col = 1
    While Cells(26, col) <> Empty and col <= 10
        'lRow = Cells(Rows.count, col).End(xlUp).Row --> to find for last non-empty row
        'lCol = Cells(26, Columns.count).End(xlToLeft).Column  --> to find for the last non-empty column
        For j = 26 To 34 ' as per your requirement of A26 to J34
            If Cells(j, col) <> Empty Then
                cnt = cnt + 1
            Else
                GoTo Result
            End If
        Next j
        col = col + 1
    Wend
    col = col - 1
Result:
MsgBox "There are [" & cnt & " Rows] and " & "[" & col & " Columns] within the range" & vbCrLf & _
            "before reaching the empty cell.", , "Counting Columns and Rows"
End Sub

Open for comments.
Thanks God bless

 

[villy]

I assume the COUNTA function will be involved.

This works for me in different ways but if you want to use COUNTA it will be different code. But this works fine with me. Hope you find it useful though.

Thanks