VBA Worksheet Function CountA using Dynamic Range

[Boffa]

Needing some help using the the CountA Worksheet function in VBA

Currently my code runs and the active cell is BG2 (BG IS COLUMN 58)

I then want to count the number of non blank cells from BF2:B2 then I want the active cell to go to the next cell down to BG3 then count the number of non blank cells from BF3:B3.

Im trying to do a for next loop using the countA worksheet function with the variables existing in the code outlined below (which provides the number of rows AND column (57) ) and i as the counter in a range but keep getting an error.

How can I make my CountA function dynamic using variables as the row and column index in the range ?

The count will always be from wherever the activecell is through to the corresponding cell in column B

each day the data changes hence I need the the CountA range to be dynamic

MY VARIABLES
RowCnt = Cells(Rows.count, 1).End(xlUp).Row

With ActiveSheet.UsedRange
colct = .Columns(.Columns.count).Column
End With

BELOW RESULTS IN AN ERROR
For i = 1 To RowCnt - 1
rst = Application.WorksheetFunction.CountA(Range(Range(Selection.Offset(0, -colct, ActiveCell.Offset(0, -1)))))
ActiveCell.Value = rst
ActiveCell.Offset(1, 0).Select
Next

 

[Norie]

Try this.

Set rng = Range("BG2")

For I = 1 to RowCnt-1
    rng.Value = Application.CountA(Range(Cells(rng.Row, "B"), rng.Offset(,-1))
    Set rng = rng.Offset(1)
Next I

 

[steve the fish]

This is how you could do that with a variable result range based on the activecell:

RowCnt = Cells(Rows.Count, 1).End(xlUp).Row
myRow = ActiveCell.Row
myCol = ActiveCell.Column

If myCol > 2 And myRow <= RowCnt Then
    For i = myRow To RowCnt
        Cells(i, myCol) = WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, myCol - 1)))
    Next
End If

 

[Boffa]

To Norie and Mr Fish thank-you so much for the quick response here much appreciated both solutions work great ! - Looks so easy once i see it coded correctly !

Thanks again !