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

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?

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

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



"ECD" wrote in message
...
| 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?

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

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.

--
Regards,
Tom Ogilvy

"ECD" wrote in message
...
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?