I changed some of your variable names (x/y don't mean much when you're
debugging).

Option Explicit

Sub SearchComments()

Dim SearchTerm As String

Dim visRows As Long
Dim procRows As Long
Dim iRow As Long
Dim iCol As Long
Dim Answer As Long


'Check active cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of comments filled list?", _
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

iRow = ActiveCell.Row
iCol = ActiveCell.Column

visRows = 0 'No. of visible rows
procRows = 0 'No. of processed rows


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
'do nothing???
Else
If LCase(Cells(iRow, iCol).Comment.Text) = "music" Then
Rows(iRow).Hidden = False
visRows = visRows + 1
Else
Rows(iRow).Hidden = True
End If
End If
iRow = iRow + 1
Loop

MsgBox visRows & vbLf & "of " & procRows
End Sub

John wrote:

Hi,

I'm trying to write a procedure that will run through a column of data and
hide the rows that do not match a particular criteria. I'm fairly new at
this and so I'm sure this is a very obvious problem! I have a column of
data every cell of which has a comment. What I want to do is to enter a
search term to search within the comments text.........

The main difficulty I'm having is getting the active cell row and column
values (which I've called "r" and "c" respectively). At the moment the
ActiveCell.Row reads empty even when I can see the damn thing with a word in
it and a comment attached!

The x and y variables will be used as a message box at the end so I can see
how many have been filtered. I'll also be putting in a dialogue box to
capture the search term (although I might save that for another post unless
anyone is feeling extra helpful).

Anyway, thanks in advance

John

Sub SearchComments()

Dim SearchTerm As String
Dim rgCurrentCell As Range
Dim x As Variant
Dim y As Variant

'Check active cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of comments filled list?",
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

x = 0 'No. of visible rows
y = 0 'No. of processed rows

Set rgCurrentCell = Cells(r, c)

Do
If IsEmpty(Cells(r, c)) Then
MsgBox "Current cell is empty"
Exit Do
End If
If rgCurrentCell.Comment.Text = "music" Then x = x + 1 Else
Rows(r).EntireRow.Hidden = True

y = y + 1
r = r + 1
Loop

End Sub

--

Dave Peterson