Excel VBA to Select Rows Down That Include Blanks

Question

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.

Are your "tables" actual Tables/ListObjects, or just regular ranges? You might find them easier to manage if you convert them to ListObjects. — Tim Williams
– Tim Williams, Commented Jun 9, 2022 at 18:01
Tables have a DataBodyRange property which return the range with table data - you can use that to determine the range to fill to — Tim Williams
– Tim Williams, Commented Jun 9, 2022 at 18:24

Tim Williams · Accepted Answer · 2022-06-09 18:57:40Z

2

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

answered Jun 9, 2022 at 18:57

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eduardo Pistorello · Accepted Answer · 2022-06-10 12:16:44Z

0

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.

answered Jun 10, 2022 at 12:16

Eduardo Pistorello

518 bronze badges

1 Comment

VBasic2008 Over a year ago

End(xlDown) will include the Total cell. You didn't account for that in your solution (e.g. last_row - 1).

Collectives™ on Stack Overflow

Excel VBA to Select Rows Down That Include Blanks

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related