ExcelBanter - Filling An Autofiltered List

You want to put the same value in the visible cells of a filtered list???

Then maybe this'll get you started:

Option Explicit
Sub testme()

Dim rngF As Range
Dim myStr As String

'get your value in the listbox into mystr
myStr = "Hi there!"

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'no visible cells, except for the header
Else
Set rngF = Nothing
On Error Resume Next
Set rngF = Intersect(ActiveCell.EntireColumn, _
.Offset(1, 0).Resize(.Rows.Count - 1)) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "activecell not in autofilter range"
Else
rngF.Value = myStr
End If
End If
End With
End Sub

It populates all the visible cells in the filtered range (not the header row) in
the activcell's column.

It kind of sounded like that's what you're doing.

Neil wrote:

Hi, I have sheet called static data with some lists on. I
have a data sheet with a lot of data on it, against which
I am writing a value from the static data sheet, im doing
this by autofiltering the main data, then using a macro
im popping up a window with a list box in it with all the
items from the static data sheet, when I double click an
item from the list box its puts the value in the active
cell on the data sheet, I then have to manually copy the
value down the filtered list, is there anyway instead of
just pasting the list box value back to the active cell I
can paste it to all the cells for the current column that
are showing in the autofiltered list ?

--

Dave Peterson