If A2="X" and B2="Y" then copy entire row including the header row

bwaaack

Board Regular
Joined
Dec 5, 2015
Messages
62
Office Version
  1. 365
Platform
  1. Windows
I am trying to copy all of the rows in a sheet that meet 2 different criteria. Including the header row.
If A2="X" and B2="Y" then copy entire row including the header row
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I am trying to copy all of the rows in a sheet that meet 2 different criteria. Including the header row.
If A2="X" and B2="Y" then copy entire row including the header row

You only want to check A2 and B2, or also A3 and B3, A4 and B4, etc.
And where do you want to paste it?
You could explain in more detail what you need.
 
Upvote 0
You only want to check A2 and B2, or also A3 and B3, A4 and B4, etc.
And where do you want to paste it?
You could explain in more detail what you need.

I would like to copy all rows in the sheet that meets the criteria. Then I would like to paste it into a new sheet.
 
Upvote 0
Try this:

Code:
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim rngFiltered As Range
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet

    Application.ScreenUpdating = False
    
    Set wsSource = Sheets("Sheet1") 'Sheet name containing the data. Change to suit.
    Set wsOutput = Sheets("Sheet2") 'Sheet name for filtered data to be outputted to. Change to suit.
    
    ActiveSheet.AutoFilterMode = False 'Remove all filters
    
    'Ensure there's at least one row with a 'X' and a 'Y' in columns A and B
    If Evaluate("COUNTIF('" & wsSource.Name & "'!A:A,""X"")") + Evaluate("COUNTIF('" & wsSource.Name & "'!B:B,""Y"")") = 0 Then
        MsgBox "There are no rows with a X and Y in columns A and B!!", vbExclamation
        Exit Sub
    End If
    
    lngLastRow = wsSource.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With wsSource.Range("$A$1:$B$" & lngLastRow)
        .AutoFilter Field:=1, Criteria1:="X"
        .AutoFilter Field:=2, Criteria1:="Y"
        Set rngFiltered = .SpecialCells(xlCellTypeVisible)
        If Not rngFiltered Is Nothing Then
            rngFiltered.Copy Destination:=wsOutput.Range("A1")
        End If
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Records have now been copied.", vbInformation

End Sub

Regards,

Robert
 
Upvote 0
Try this:

Code:
Sub copy_entire_row()
    Dim sh As Worksheet
        
    Set sh = Sheets("Sheet1")
    sh.Range("A1").AutoFilter 1, "X"
    sh.Range("B1").AutoFilter 2, "Y"
    Sheets.Add
    sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    sh.ShowAllData
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top