Copy formula across row with dynamic range

BG1983 · Apr 9th 2016

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.

Thanks!

rollis13 · Apr 9th 2016

Re: Copy formula across row with dynamic range

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

Code

Option Explicit


Sub test()


    Dim x As Long
    Dim lr As Long


    For x = 1 To 46
        lr = Cells(Rows.Count, x).End(xlUp).Row
        Cells(16, x) = WorksheetFunction.CountA(Range(Cells(17, x), Cells(lr, x)))     ' -1 (or change "17" to "18" if the table has headers)
    Next x


End Sub

Display More

skywriter · Apr 9th 2016

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.

Code

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

Copy formula across row with dynamic range

Participate now!

Share