How to jump to next column using VBA?

[Ciganjo]

Hi, I have a little issue with columns.

A-------B----------C-------D
one---- two------three----four
one---- two------three ----four---------Count A =
one---- two------three ----four---------Count B =
one---- two------three ----four---------Count C =
one--------------three----four----------Count D =
one--------------three
------------------three
------------------three

I want to count how many cells are filled in each column, but I need this to be done autonomously via a macro i.e. when I run the macro, it should give me the total number of filled cells in Col A, Col B, Col C etc... (the number of columns is undefined, depends on how much data coming in). I have the code to count one column, but don't know how to jump to the next one:

Sub CountFrames()

Dim StaticColumn
Dim Row

StaticColumn = 15
Row = 13

Do Until WorksheetFunction.CountA(StaticColumn) = 0
If WorksheetFunction.CountA(StaticColumn) > 0 Then
Cells(Row, 10).Select

Cells(Selection.Row, Columns.Count).End(xlToLeft).Offset(, 1).Select

'offset(C15,0,1)
ActiveCell.FormulaR1C1 = "=COUNTA(C15)"
Row = Row + 1
StaticColumn = StaticColumn + 1
Else
MsgBox "Empty"
End If
Loop

End Sub


Can anyone help?

Many thanks!!

 

[Norie]

Are you trying to put a formula in the worksheet to count each column.

Or are you trying to do the count in the code and put the results on the worksheet?

 

[Ciganjo]

Count in the code and output into worksheet

 

[Norie]

Where do the results go?

 

[Ciganjo]

Ideally the results need to be anywhere before the start of the column I want to count. So if i want to count columns F, G, H, I etc... the results would need to go somewhere before column F

 

[Norie]

I thought you were counting columns from A?

That's what I wrote this code for.

Option Explicit
 
Sub CountCols()
Dim rngCol As Range
Dim rngDst As Range
Dim arrCnts()
Dim I As Long
 
    Set rngCol = Range("A1")
 
    While rngCol.Value <> ""
        ReDim Preserve arrCnts(I)
        arrCnts(I) = Application.CountA(rngCol.EntireColumn)
        I = I + 1
        Set rngCol = rngCol.Offset(, 1)
    Wend
 
    Set rngDst = rngCol.Offset(, 3)
 
    rngDst.Value = "A"
 
    rngDst.AutoFill rngDst.Resize(I), xlFillDefault

    rngDst.Offset(, 1).Resize(I) = Application.Transpose(arrCnts)
 
End Sub