Excel VBA to Select Rows Down That Include Blanks-CodePudding

I'm trying to create a macro to copy cells down an entire column of a table in hopes of acting as a 'refresh' in case those formulas were altered or replaced.

I have multiple tables in the same sheet so I can't select the table name because they constantly change.

What I'm thinking of is having a bottom row with a keyword that VBA can select down until they hit the keyword and select those cells to copy the formulas down.

The thing is that I have multiple tables and they would all have the bottom row of keywords.

When I recorded a macro, I have to Control Shift Down multiple times to account for missing rows which I imagine wouldn't always be the case. This is what scares me for this macro since sometimes a table would have no missing data so the xlDown function would select more data than it should.

Here is what I recorded:

Sub Macro9()
'
' Macro9 Macro
'

'
    ActiveCell.Offset(3, 2).Range("A1").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
End Sub

Here is an example of the column I am trying to restore formulas on:

I want the formula below "Total Price" to fill down until it hits the word "Total". Note that formulas are hidden if there is no data elsewhere in the sheet.

There are multiple tables on this sheet so this would have to work in different sections of te same sheet.

CodePudding user response：

If you have actual Tables/Listobjects then you could do something like this:

Sub FillDownFormulas()

    Dim lo As ListObject, col As Range
    
    For Each lo In ActiveSheet.ListObjects
        For Each col In lo.DataBodyRange.Columns
            If col.Cells(1).HasFormula Then         'first cell in column has a formula?
                col.Formula = col.Cells(1).Formula  'fill formula to rest of column
            End If
        Next col  'next column
    Next lo       'next table

End Sub

CodePudding user response：

One of the most simple ways (for people that usually don't use VBA) is to get the number of the last row in your table. You can do that by counting values in table or with your own code by using a column that is always filled, like:

last_row = Range("B2").end(xldown).row

With last_row value you can fill your formula in ranges, like:

Range("C2").value = 'Your Formula here     
Range("C2").AutoFill Destination:=Range("C2:C" & last_row)

You can do that to every column that you want.