Macro to Count Entries of Columns

  • Thread starter Thread starter Saad.Sakir
  • Start date Start date
S

Saad.Sakir

How can you guys add to the following macro :


Dim Test_1
Dim Count_Test_1
Test_1 = Range("B5", "B" &
Range("B65536").End(xlUp).Row - 1)
Count_Test_1 = WorksheetFunction.Count(Test_1)
Range("B" & Range("B65536").End(xlUp).Row + 1) =
Count_Test_1



The above Macro counts the data in Column B and Display the result at
the bottom of the Column

problem is:

I need to Count all the entries of the adjacent Columns (i.e. Columns
C, D, E, F, G, H, I, ... , until end of Columns containing data)
 
Try this:

Sub AAAA()
Dim Test_1 As Range, Count_Test_1 As Long
Dim CurrCol As Integer, LR As Long
Range("B5").Activate
CurrCol = ActiveCell.Column
Do While Application.CountA(Columns(CurrCol)) > 0
LR = Cells(Rows.Count, CurrCol).End(xlUp).Row
Set Test_1 = Range(ActiveCell, Cells(LR, CurrCol))
Count_Test_1 = WorksheetFunction.Count(Test_1)
Cells(LR + 1, CurrCol).Value = Count_Test_1
Cells(5, CurrCol + 1).Activate
CurrCol = ActiveCell.Column
Loop
Set Test_1 = Nothing
End Sub

Hope this helps,

Hutch
 
Back
Top