+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    ECD
    Guest

    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?

  2. #2
    William
    Guest

    Re: 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

    [email protected]

    "ECD" <[email protected]> wrote in message
    news:[email protected]...
    | 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?



  3. #3
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1