VBA to select visible cells in filtered list

[Scrubbrush]

Folks,
I've visited MrExcel previously to find various solutions and learn new tricks. I've spent about 4 hours searching the forums for the solution to my current problem and i think I'm close, but i cannot seem to find the right thread to get this done. I finally gave up, joined the community, and this is my first forum post/question. I love this site!

I'm a fairly advanced excel user from a formulas standpoint, but a bit of a VBA rookie.

I'm trying to write VBA code that will:
1. Select the first visible cell underneath the header of a filtered list (I think I have a good solution for this but I'm open to suggestions)
2. Select all cells (some blank, some with data) in column EE from the first visible cell underneath the header (EE14) to the bottom of the range (as defined by "LastRow" in macro) when the list is filtered (this where I'm having difficulty).
3. Paste a formula into the selected (visible) cells (this should be easy if i can solve #2 above)

It sounds easy, but i cannot seem to find the right combination of "SpecialCells(xlCellTypeVisible)" and/or "End(xlDown)" to select just the cells that are visible when the list is filtered.

Header row = row 14 (due to need for summary/calculator at top of sheet).
Columns are A:EE
Data range is dynamic. It currently has 1,624 rows (not including header) but could have 1,200 rows tomorrow and 2,250 the day after and so on.

The macro i have already filters the range to only show rows that need the formula (which is a simple vlookup). Please note, the filtered rows can/will change from one day to the next. There may be 6 rows today (say, 15,27,168,289,888,1225) and 55 rows tomorrow (say, 16,17,22,108,456,...1467, etc.)

' Macro1 Macro
' identifies last row. I use this elsewhere in the macro to re-set the filters if/when the number of rows changes.
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
' Cell EE3 contains the formula i need to copy into first visible cell under header in column EE when list if filtered. I keep the formula in a cell and copy/paste-special the formula into the 'selected cell' rather than write the formula into the macro since the 'selected cell' (next step) will chnage every day and i need the formula to also change.
Range("EE3").Select
Selection.Copy
' selects header(EE14) in column EE and then moves down to first visible cell under header. This seems to work fine. It's the cleanest solution i could find in the forums. The 'selected cell' could be any cell from EE15 to EE1164, depending on how the list if filtered.
Range("EE14").Select
With ActiveCell
With .Offset(1, 0).Resize(Rows.Count - .Row, 1)
.SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With
End With
' paste-special formula from EE3 to the "selected cell" (again, could be any cell from EE15 to EE1164, depending on filters) . This works fine. I don't think i can embed the formula in the macro since the 'selected cell' can/will change from day to day. The formula is a simple vlookup.
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'This is where i need help. I now have the formula successfully pasted into the first visible cell under the header in column EE (and that cell is currently the "selected" cell. Again, this cell might be EE15 today and EE143 tomorrow and will change again the next day). All I need to do at this point is copy the formula down column EE across all visible cells (some with data, some blank).
' I have gone through the forums and borrowed and butchered various combinations of the following code and cannot get this to work.
Selection.FillDown (does not work if all cells are blank because it copies formula to row 100000+)
Range(Selection & LastRow).Select '(i don't understand why this doesn't work, but it does't) Range(Selection).AutoFill Destination:=Range(Selection & LastRow)

'Selection.FillDown
Range(Selection & LastRow).End(xlDown).Select


Any help in selecting the visible cells in column EE and pasting the formula from cell EE3 would be greatly appreciated.

 

[Snakehips]

Scrubbrush,

Try test this revised code and see if advances the cause any.
It will put EE3 formula into the visible cells but I'm unsure as to how the relativity of the formulas suits your need.

However it may be a starting point.

Sub test()
' Macro1 Macro
' identifies last row. I use this elsewhere in the macro to re-set the filters if/when the number of rows changes.
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
' Cell EE3 contains the formula i need to copy into first visible cell under header in column EE when list if filtered. I keep the formula in a cell and copy/paste-special the formula into the 'selected cell' rather than write the formula into the macro since the 'selected cell' (next step) will chnage every day and i need the formula to also change.
'This is where i need help.


Range("EE15:EE" & LastRow).SpecialCells(xlCellTypeVisible).Formula = Range("EE3").Formula


End Sub

HOPE THAT HELPS.