Searching multiple columns

**Erenagh** · 06-16-2011, 02:38 PM

I want to develop a macro that will allow me to search multiple columns and then copy the respective row onto another sheet

Search 1:
Column A = "y"
Column B = "" <~Blank

Copy to sheet 2

Search 2:
Column A = "y"
Column C = "y"

Copy to sheet 2

I have code the I used for 1 search term that works great from tigeravatar:

    Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
   
    Dim ws2DestRow As Long: ws2DestRow = 1

    Dim z As Long

    For z = 4 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
        If Trim(LCase(ws1.Cells(z, "A").Value)) = "y" Then
            ws2.Range("C" & ws2DestRow).EntireRow.Value = ws1.Cells(z, "A").EntireRow.Value
            ws2DestRow = ws2DestRow + 1
        End If
    Next

**tigeravatar** · 06-16-2011, 03:28 PM

Erenagh,

Could give this a try:

    Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
   
    Dim ws2DestRow As Long: ws2DestRow = 1
    Dim z As Long
    
    For z = 4 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
        If Trim(LCase(ws1.Cells(z, "A").Value)) = "y" _
        And Trim(ws1.Cells(z, "B").Value) = vbNullString Then
            ws2.Range("C" & ws2DestRow).EntireRow.Value = ws1.Cells(z, "A").EntireRow.Value
            ws2DestRow = ws2DestRow + 1
        ElseIf Trim(LCase(ws1.Cells(z, "A").Value)) = "y" _
        And Trim(LCase(ws1.Cells(z, "C").Value)) = "y" Then
            ws2.Range("C" & ws2DestRow).EntireRow.Value = ws1.Cells(z, "A").EntireRow.Value
            ws2DestRow = ws2DestRow + 1
        End If
    Next

Hope that helps,
~tigeravatar