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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
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
 

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,315
Messages
5,641,482
Members
417,211
Latest member
loadius

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
Top