Using VBA to filldown filtered table

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Suppose there is one column that has blank data after filtering a table. How do I use VBA to fill these blank columns with a formula using filldown? The formula is on one of the cells in that same column. I tried the code below and it fills down way below the last visible row and it also copied formatting which I did not want, such as strike through, etc.

VBA Code:
With Database.UsedRange
    .Resize(.Rows.count - 1).Offset(1).Columns("H"). _
       SpecialCells(xlCellTypeVisible).FillDown
End With
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
How about this?
VBA Code:
Dim s
With Database.UsedRange
    s = .Range("H2").Formula
    .Resize(.Rows.count - 1).Offset(1).Columns("H"). _
       SpecialCells(xlCellTypeVisible).Formula = s
End With
I assume your formula can at least be found in H2
 
Upvote 0
How about this?
VBA Code:
Dim s
With Database.UsedRange
    s = .Range("H2").Formula
    .Resize(.Rows.count - 1).Offset(1).Columns("H"). _
       SpecialCells(xlCellTypeVisible).Formula = s
End With
I assume your formula can at least be found in H2
Thanks for helping but I forgot to mention that my formula uses relative references. This would work if the formula uses absolute references
 
Upvote 0
Then try with R1C1
VBA Code:
Dim s
With Database.UsedRange
    s = .Range("H2").FormulaR1C1
    .Resize(.Rows.count - 1).Offset(1).Columns("H"). _
       SpecialCells(xlCellTypeVisible).FormulaR1C1 = s
End With
 
Upvote 0
Solution
Then try with R1C1
VBA Code:
Dim s
With Database.UsedRange
    s = .Range("H2").FormulaR1C1
    .Resize(.Rows.count - 1).Offset(1).Columns("H"). _
       SpecialCells(xlCellTypeVisible).FormulaR1C1 = s
End With
Worked well for me, thanks!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top