Hi,

I'm trying to write some code so that I can filter out rows based on a
search a cell's comments box. I've managed (with the help of Dave and
Wouter, thanks guys) to produce the code at the bottom of this post and this
works very nicely, hiding alls rows that do not match the criteria entered
in the InputBox.

My problem now is that when I autofilter a range, I can drag cell contents
across the hidden rows and the data is only duplicated in the visible rows.
Fine, that's what I want. But with my code, I hide the appropriate rows,
drag a cell's contents down over the hidden rows, but when I unhide the
hidden rows I find that the data has been copied across all rows, visible
and hidden!

I notice that playing around with the macro recorder that there appears to
be a difference between "FillDown" and "AutoFill", see below. Can anyone
help with this curious difference? I always thought that AutoFilter only
hides rows anyway.

Best regards

John


MACRO RECORDER EXAMPLE

Selection.AutoFilter Field:=1, Criteria1:="Rabbit"
Range("G9").Select
ActiveCell.FormulaR1C1 = "Test Word 1"
Range("G9").Select
Selection.FillDown

Selection.AutoFilter Field:=1
Rows("16:19").Select
Selection.EntireRow.Hidden = True
Range("H9").Select
ActiveCell.FormulaR1C1 = "Test Word 2"
Range("H9").Select
Selection.AutoFill Destination:=Range("H9:H23"), Type:=xlFillDefault
Range("H9:H23").Select
Rows("15:20").Select
Selection.EntireRow.Hidden = False


SEARCH CELL COMMENTS PROCEDURE

Sub SearchCellComments()
Dim SearchTerm As String
Dim iRow As Long
Dim iCol As Long
Dim visRows As Long
Dim procRows As Long
'
If MsgBox("Is cell at top of comments filled list?", _
vbYesNo + vbQuestion, _
"Start Process") = vbNo Then Exit Sub
'
iCol = ActiveCell.Column
iRow = ActiveCell.Row
'
visRows = 0 'No. of visible rows
procRows = 0 'No. of processed rows

SearchTerm = LCase(InputBox(Prompt:="Please enter search term"))

Do
If IsEmpty(Cells(iRow, iCol)) Then
MsgBox "Current cell is empty"
Exit Do
End If

procRows = procRows + 1
If Cells(iRow, iCol).Comment Is Nothing Then
Rows(iRow).Hidden = True
Else
'If LCase(Cells(iRow, iCol).Comment.Text) = SearchTerm Then
If InStr((LCase(Cells(iRow, iCol).Comment.Text)), SearchTerm)
Then
Rows(iRow).Hidden = False
visRows = visRows + 1
Else
Rows(iRow).Hidden = True
End If
End If
iRow = iRow + 1
Loop

MsgBox CStr(visRows) & " of " & CStr(procRows) & " records found."
'Application.StatusBar = CStr(visRows) & " of " & CStr(procRows) & "
records found"
'Application.StatusBar = False
End Sub