AutoFilter & Hidden Rows
 

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

AutoFilter & Hidden Rows
 

Hi Dave,

Thanks very much for this. That's interesting about the xlcelltypevisible,
but would this allow me to then manually edit the range in the drag fill way
I was mentioning? I'm assuming that this would only work if I wanted to
refer to them programatically (which I might do on another occasion!)? What
do you think?

Thanks also for the Instr tip. I've had a look at the help file and I'm not
sure I fully understand this. Would your suggestion test for whole words
only? Is that the purpose?

Anyway, thank for your help and apologies for returning with lots of curious
questions!

Best regards

John



"Dave Peterson" wrote in message
...
There's a difference between rows hidden by autofilter and hidden, er,
manually--even by a macro.

But you can get to the visible cells in a column of a range with something
like:

dim myVRng as range
dim myArea as range
set myVrng = nothing
on error resume next
set myvrng = .range("a1:a99").cells.specialcells(xlcelltypevisi ble)
on error goto 0

if myVRng is nothing then
'no visible cells in range
else
for each myArea in myvrng.areas
myarea.value = "hi there"
next myarea
end if

=======

and instr() has an option to compare text:

If InStr((LCase(Cells(iRow, iCol).Comment.Text)), SearchTerm)
could be:
If InStr(1, Cells(iRow, iCol).Comment.Text, searchterm, vbTextCompare) 0
Then


John wrote:

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

--

Dave Peterson

AutoFilter & Hidden Rows
 

You can use the equivalent of xlcelltypevisible cells by selecting your range.
Hit F5 (or edit|goto), click special, check visible cells only.

You can't drag down, but you could type and hit control-enter to fill the
selected cells. Works nice with constants and formulas. But double check.
Absolute references might give you trouble ($a$1 v a1) if you're not careful.

And instr() looks at strings--not words. It can be partial words or multiple
words:

msgbox instr(1, "this is a test of a lo", "s a te",vbtextcompare)



John wrote:

Hi Dave,

Thanks very much for this. That's interesting about the xlcelltypevisible,
but would this allow me to then manually edit the range in the drag fill way
I was mentioning? I'm assuming that this would only work if I wanted to
refer to them programatically (which I might do on another occasion!)? What
do you think?

Thanks also for the Instr tip. I've had a look at the help file and I'm not
sure I fully understand this. Would your suggestion test for whole words
only? Is that the purpose?

Anyway, thank for your help and apologies for returning with lots of curious
questions!

Best regards

John

"Dave Peterson" wrote in message
...
There's a difference between rows hidden by autofilter and hidden, er,
manually--even by a macro.

But you can get to the visible cells in a column of a range with something
like:

dim myVRng as range
dim myArea as range
set myVrng = nothing
on error resume next
set myvrng = .range("a1:a99").cells.specialcells(xlcelltypevisi ble)
on error goto 0

if myVRng is nothing then
'no visible cells in range
else
for each myArea in myvrng.areas
myarea.value = "hi there"
next myarea
end if

=======

and instr() has an option to compare text:

If InStr((LCase(Cells(iRow, iCol).Comment.Text)), SearchTerm)
could be:
If InStr(1, Cells(iRow, iCol).Comment.Text, searchterm, vbTextCompare) 0
Then


John wrote:

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

--

Dave Peterson


--

Dave Peterson