CODEWITHSUNDEEP
excelvba
Kevin
Kevin

Reputation: 7

Loop through counting rows and paste row count to a specific cell

I have a selection of data that has a dynamic number of columns, and under each column there is a dynamic list of values.

I want to go through each column and count the number of items minus the two header fields and place it that count as a value in a specific position.

So I have two rows of headers and then a blank row and then the data.

Private Sub Worksheet_Activate()
Dim LastCol As Integer
Dim I As Long
Dim RC As Long

    With ActiveSheet
        LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    End With

    For I = 1 To LastCol
        RC = Range("I:I" & Rows.Count).End(xlUp).Row
        Cells(3, I).Value = RC
    Next I

End Sub

I know the problem is in the loop section but I don't know how to call the rows in a number format.

Upvotes: 0

Views: 6357

Answers (1)

peege
peege

Reputation: 2477

What this does: If I understood the question correctly. This will skip the two header rows, and count to see how many rows have data in them for each column. Then place the count in the third row on each column.

You've got a few issues:

  • It's a good practice to always access the properties by using a "." Like, LastCol = .Cells.

  • You ended your With Statement too soon.

  • If you are using a variable to iterate through the columns, you should use .Cells, instead of Range, and use the column number instead of Letters.

  • You should use Long type variables for loops and row counts instead of Integer.

  • You might want to put this code in a module, and then just call it from the worksheet activate event. That way you can call it from a button or any other time, if you need to update the counts.

  • The Count should = RC - 3 to account for the header rows.

In the worksheet code:

Private Sub Worksheet_Activate()

    Call CountColumnRows

End Sub

Then in a module, place this: Allowing you to call it from any source, and at any time.

Sub CountColumnRows()

Dim LastCol As Long, I As Long, RC As Long

    With ActiveSheet
        LastCol = .Cells(2, Columns.count).End(xlToLeft).column

        For I = 1 To LastCol
            RC = .Cells(Rows.count, I).End(xlUp).row   'I as variable column Number
            .Cells(3, I).Value = RC - 3                 'Compensate for the headers and Count Row
        Next I

    End With

End Sub

Screenshot

Upvotes: 1

Related Questions