VBA to address first visible cell in Column "D" after filtering

[EagleOne]

2003

Having difficulty determining how to address in VBA the first visible
cell in Column "D" after the FilterCriteria changes.

Partial code follows:

Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<>"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

up to this point all is well


Set Filter2 = Nothing
Set Filter2 = Filter1.SpecialCells(xlCellTypeVisible)
'I thought that (after) Filter2 the Visible cells would be
' reset which they are on the screen
myCell.Offset(0, 3).Formula = Filter2.Range("E2").Formula

I thought that after filtering Filter1 that I could Set Filter2
(visable cells) and that Filter1.Range("E2").Formula and
Filter2.Range("E2").Formula would be different
but they are the same.

In short, I want all visible cells after filtering in Column E to have
exactly the same formula as in the first visible cell in Column E each
time that the FilterCriteria changes. What happens is that all visible
cell formulas are the same as the very first cell in E irrespective of
the FilterCriteria.

I need to know how in VBA to address that first cell in Column E;
AFTER each FilterCriteria changes.

 

[Dave Peterson]

Maybe you could use something like:

dim myVRng as range
....

with worksheets("constants input")
with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
'do nothing, only the header is visible
else
'go to column 5 (E) and come down a row and subtract a row to
'ignore the header
set myvrng = .columns(5).resize(.rows.count,1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)

myvrng.formular1c1 = myvrng.cells(1).formular1c1
end if
end with
end with

Untested, uncompiled. Watch for typos.

myVRng is the visible cells in column 5 of the filtered range. myVRng.cells(1)
is the first cell in that visible range.

 

[EagleOne]

Dave,

I think you have!! Thanks so much!!

Dennis