Filldown VBA macro

[oliviar]

Hi guys,
I need a macro that will filldown formula only to visible cells based on the formula in the selected cell. ColA is going to be filtered for chunks of data, and colB needs different formula depending on the results in colA, but the actual formula isn't what I'm stuck on... its the bloody filldown.

The invisible rows are only at the top and the bottom of the range i need to filldown - none are invisible in the middle, if that helps.

I don't understand why what I'm writing won't work.. but I'm trying to ask it to select the current cell, then filldown... but nothing.

I think I might need to use xlup and a range, but I don't know

 

[diddi]

could you use vba to calculate the values in colB directly, rather than filling formulas

 

[oliviar]

Sadly it HAS to be done with formula, because the formula is creating random numbers.

However, its okay, because I did it!
The solution did involve ranges, and xlup and down. Then I united the ranges with the union function to enable the filldown.
Excel 2003 will not filldown without a selection to fill into.

Solution:

Sub blanks()
'Coded by Olivia
ActiveSheet.Range("b1").End(xlDown).Select
Set rg1 = ActiveCell
Set rg = Range("b:b")
Set rg = rg.SpecialCells(xlCellTypeVisible)
Set rg = rg.SpecialCells(xlCellTypeBlanks)
rg.Select
Union(rg1, rg).Select
selection.filldown
End Sub

 

[diddi]

well done! hows the big smoke today? up to the third season for the day yet

 

[oliviar]

How did you KNOW... it just got cold... Was unbearably hot at lunch. Now I'm in my jumper.

 

[diddi]

i grew up down there...



now im in the sunny northern lands growing all your fruit for you

 

[asking]

Name a dynamic range. In the Menu go to, Formula, namemanager, click new. In the name box type... Show_colB, in the refers to box copy and paste this formula If your
data starts in row 4. If not Change the 4's in the formula to the row number your data starts in.

=OFFSET($B$4,0,0,COUNT(OFFSET(Easy!$A$4,0,0,9999)),1)

If your formula is in the top row of your data in col. B after filtering, insert this into your macro to fill down in the right places.

Application.Goto Reference:="Show_colB"
Selection.FillDown

If you still have difficulty you may need to do this ~

Application.Goto Reference:="Show_colB"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FillDown

Cheers.