Copy formula across row with dynamic range

  • Hi, I have a table (Table1) starting at row 17 and ends at different #s depending on the workload for that day. Columns will always be from A:AT. In row 16, just above the table, I am trying to figure out the best VBA formula to count the number of non blank cells in each column and display that total for each column.. so, in A16,B16,C16, etc...
    I was using the formula =SUBTOTAL function but am running into problems... Any help on that would greatly be appreciated!

    Another random question: I made a custom slicer (colors) and then applied it to my 6 slicers in the worksheet but noticed that the VBA code for doing that (with record) is ridiculously long... is there a more efficient way to do that? I was duplicating SlicerStyleDark1 , then modifying it (Now SlicerStyleDark2 after the duplication) to have the "Whole Slicer" background black with a font of light blue, and then changing the header font to light blue. Obviously I can just leave the slicer as is, but any suggestions there would be great... just dont know and havent seen how to do that before.


  • Re: Copy formula across row with dynamic range

    What is your meaning for "VBA formula" ?
    Anyway, this macro should do the job:

  • Re: Copy formula across row with dynamic range

    Assuming you are using an actual table and the name of the table is Table1 and you have a header row.

    The following code will put a permanent formula in the row above your header row that will take into account adding more data to your table.

    Run it once to create the formulas and you are done.

    Sub AddFormulas()
    Dim r As Range, strFormula As String
        For Each r In ActiveSheet.ListObjects("Table1").HeaderRowRange
            strFormula = "=COUNTA(" & r.ListObject.Name & "[" & r.Value & "])"
            r.Offset(-1).Formula = strFormula
        Next r
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!