In Excel, how do I select rows in which a certain value appears?

[Guest]

I'm trying to make a macro that will select all rows in which a specific
value appears in a column, copy them, then paste them into another sheet.
Using autofilter only selects specific row numbers in the macro, so that
didn't work for me. Can anyone help with this?

 

[William]

Hi ECD

This assumes you filter for a value of "XYZ" in Column A of Sheet1 and then
copy the entire row of the filtered data (including row 1) to Sheet 2.

Sub test()
Application.ScreenUpdating = False
Dim r As Range
With Sheets("Sheet1")
..AutoFilterMode = False
Set r = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
r.AutoFilter Field:=1, Criteria1:="XYZ"
Set r = r.SpecialCells(xlCellTypeVisible).EntireRow
..AutoFilterMode = False
End With
r.Copy Sheets("Sheet2").Range("A1")
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I'm trying to make a macro that will select all rows in which a specific
| value appears in a column, copy them, then paste them into another sheet.
| Using autofilter only selects specific row numbers in the macro, so that
| didn't work for me. Can anyone help with this?

 

[Tom Ogilvy]

If you filter the entire range, autofilter should work and would be the
easiest. Perhaps you have some completely blank rows in your data which
causes only a subset of the range to be filtered. If so, select the
entirearea, then apply the autofilter. You should then be able to use the
approach you have already developed.