Macro to filter out mismatched opposites. Dont look unless you want a challenge.. lol

[saintsphan]

I am trying to write a macro that copies the data from esoms.xls to esomsmacro.xls. It then compares A1 to B1. If they both contain " A" or " B" I want to delete the row. I have this much working. Next I need to see if the same 2 cells both have "A-" or "B-" and delete the row if they do. I think I was going about this the wrong way by deleting the rows that match my criteria rather than copy the rows to a new sheet when they have criteria that I am looking out for. If A1 has " A" and B1 has " B" then I think I should copy that to a new sheet. I need to do this for each row until I come to a blank row. Any help is greatly appreciated and Ill try my best to answer any questions.

ex.
A1 : MT -005- -MT B B1 : 1-MT -GRND -MTB EGRD0002 A

because of the A and B not matching I need to keep these.

here is what i started and started coming to some issues. I have made some of the code into comment to help in my troubleshooting. Thanks in advance:

Sub esoms()
'
'
    'paste from query to new excel sheet
    Windows("esoms.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("esomsmacro.xls").Activate
    Cells.Select
    ActiveSheet.Paste
    
    'Hide Row A
    'Rows("1:1").Select
    'Selection.EntireRow.Hidden = True
    
    'Autofit coulumns A and B
    'Columns("C:C").Select
    'Range("C1").Activate
    'Selection.EntireColumn.Hidden = True
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    
    'Compares A train on Columns C and D
    Range("C1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" A"",RC[-2],1)),""No"",""Yes"")"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C50000"), Type:=xlFillDefault
    Range("C1:C50000").Select
    
    Range("D1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" A"",RC[-2],1)),""No"",""Yes"")"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D50000"), Type:=xlFillDefault
    Range("D1:D50000").Select
    
    'Compares B train on Columns E and F
    Range("E1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" B"",RC[-4],1)),""No"",""Yes"")"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E50000"), Type:=xlFillDefault
    Range("E1:E50000").Select
    
    Range("F1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" B"",RC[-4],1)),""No"",""Yes"")"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F50000"), Type:=xlFillDefault
    Range("F1:F50000").Select

   'Compares A train on Columns G and H
    Range("G1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" A-"",RC[-6],1)),""No"",""Yes"")"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("G1:G50000"), Type:=xlFillDefault
    Range("G1:G50000").Select
    
    Range("H1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" A-"",RC[-6],1)),""No"",""Yes"")"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H50000"), Type:=xlFillDefault
    Range("H1:H50000").Select
    
    'Compares B train on Columns I and J
    Range("I1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" B-"",RC[-8],1)),""No"",""Yes"")"
    Range("I1").Select
    Selection.AutoFill Destination:=Range("I1:I50000"), Type:=xlFillDefault
    Range("I1:I50000").Select
    
    Range("J1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" B-"",RC[-8],1)),""No"",""Yes"")"
    Range("J1").Select
    Selection.AutoFill Destination:=Range("J1:J50000"), Type:=xlFillDefault
    Range("J1:J50000").Select
   
   'Compares Train B Columns K and L for B and spaceB
    Range("K1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(""B"",RC[-10],1)),""No"",""Yes"")"
    Range("K1").Select
    Selection.AutoFill Destination:=Range("K1:K50000"), Type:=xlFillDefault
    Range("K1:K50000").Select
    
    Range("L1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" B"",RC[-10],1)),""No"",""Yes"")"
    Range("L1").Select
    Selection.AutoFill Destination:=Range("L1:L50000"), Type:=xlFillDefault
    Range("L1:F50000").Select
   
   'Compares Train A Columns M and N for A and spaceA
    Range("M1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(""A"",RC[-12],1)),""No"",""Yes"")"
    Range("M1").Select
    Selection.AutoFill Destination:=Range("M1:M50000"), Type:=xlFillDefault
    Range("M1:M50000").Select
    
    Range("N1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND("" A"",RC[-12],1)),""No"",""Yes"")"
    Range("N1").Select
    Selection.AutoFill Destination:=Range("N1:N50000"), Type:=xlFillDefault
    Range("N1:N50000").Select
   
       'delete rows with blanks
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   
  
' Remove hidden rows from all sheets
For i = 1 To Worksheets.Count
If Worksheets(i).Visible Then
Worksheets(i).Select
ActiveCell.SpecialCells(xlLastCell).Select
k = ActiveCell.Row
For j = 1 To k
If Rows(j).Hidden Then
Rows(j).Hidden = False
Rows(j).Delete
End If
Next j
End If
Next i
If Worksheets(1).Visible Then Worksheets(1).Select

' Remove hidden columns from all sheet
For i = 1 To Worksheets.Count
If Worksheets(i).Visible Then
Worksheets(i).Select
ActiveCell.SpecialCells(xlLastCell).Select
k = ActiveCell.Column
For j = 1 To k
If Columns(j).Hidden Then
Columns(j).Hidden = False
Columns(j).Delete
End If
Next j
End If
Next i
If Worksheets(1).Visible Then Worksheets(1).Select

'check if all Yes columns C-F - C and D for spaceA - E and F for spaceB
Range("C1").Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" And ActiveCell.Offset(0, 2) = "Yes" And ActiveCell.Offset(0, 3) = "Yes" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

'check if all No columns C-F - C and D for spaceA - E and F for spaceB
'Range("C1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" And ActiveCell.Offset(0, 2) = "No" And ActiveCell.Offset(0, 3) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if Yes Yes columns C-D no on E-F - C and D for spaceA - E and F for spaceB
'Range("C1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" And ActiveCell.Offset(0, 2) = "No" And ActiveCell.Offset(0, 3) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if No No columns C-D yes on E-F - C and D for spaceA - E and F for spaceB
'Range("C1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" And ActiveCell.Offset(0, 2) = "Yes" And ActiveCell.Offset(0, 3) = "Yes" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all Yes columns G-J - G and H for A- - I and J for B-
'Range("G1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" And ActiveCell.Offset(0, 2) = "Yes" And ActiveCell.Offset(0, 3) = "Yes" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all No columns G-J - G and H for A- - I and J for B-
'Range("G1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" And ActiveCell.Offset(0, 2) = "No" And ActiveCell.Offset(0, 3) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if Yes Yes columns G-H no on I-J - G and H for A- - I and J for B-
'Range("G1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" And ActiveCell.Offset(0, 2) = "No" And ActiveCell.Offset(0, 3) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if No No columns G-H yes on I-J - G and H for A- - I and J for B-
'Range("G1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" And ActiveCell.Offset(0, 2) = "Yes" And ActiveCell.Offset(0, 3) = "Yes" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all Yes columns K-L - K and L for B and _B
'Range("K1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all No columns K-L - K and L for B and _B
'Range("K1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all Yes columns M-N - M and N for A and _A
'Range("M1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "Yes" And ActiveCell.Offset(0, 1) = "Yes" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop

'check if all No columns M-N - M and N for A and _A
'Range("M1").Select
'Do Until IsEmpty(ActiveCell)
'If ActiveCell.Text = "No" And ActiveCell.Offset(0, 1) = "No" Then
'ActiveCell.EntireRow.Delete
'Else
'ActiveCell.Offset(1, 0).Select
'End If
'Loop


End Sub

[mikerickson]

Try this

Dim outputArray() as String
Dim i as Long, outPoint as Long

With Workbooks("esoms.xls").Worksheets("Sheet1").Range("A:A")
    With Range(.Cells(1, 2), .Cells(.Rows.Count).End(xlUp))
        Redim outputArray(1 to .Rows.Count, 1 to .Columns.Count)
            For i = 1 to .Rows.Count
                With .Rows(i)
                    If Not((.Cells(1,1) = "A" and .Cells(1, 2)="A") Or (.Cells(1,1) = "A-" and .Cells(1, 2)="A-")) Then
                        outPoint = outPoint + 1
                        outputArray(outpoint, 1) = .Cells(1,1)
                        outputArray(outpoint, 2) = .Cells(1,2)
                     End If
                 End With
            Next i
        Workbooks("esomsmacro.xls").Worksheets("Sheet1").Range("A1").Resize(.Rows.Count, 2).Value = ouputArray
    End With
End With

[saintsphan]

Mikerickson - Thank you for the incredibly quick response. Im not sure if I wasnt accurate in my information or if im posting your code in the wrong place but It dont seem to do anything at all when I run it. The files I have are too big to attach on here. Is there a way I can get them to you so you can better see what im trying to accomplish ?

thanks again

[watersev]

hi saintsphan, welcome to ExcelForum, please amend your posted code as per Forum Rules, #3:
http://www.excelforum.com/forum-rule...rum-rules.html

[saintsphan]

hi saintsphan, welcome to ExcelForum, please amend your posted code as per Forum Rules, #3:
http://www.excelforum.com/forum-rule...rum-rules.html

Sorry for that. Wont happen again

[saintsphan]

I have made the 2 files shorter and uploaded them. Didnt think of that before but I hope that helps some.

[mikerickson]

I cannot figure out what you are doing.
You say that if both cells contain "A" that you want to delete them, but
in esomsmacro, row one has
4KV -001- -4KVESWGR 4A
and
1-4KV -CKTBRK-4KVEBKRTGB A8

both of which have A.

I don't understand.
Could you make an example with some shorter, simpler data and fewer than the 2,000 rows in your attached workbooks.

[saintsphan]

I understand what you are saying.. Thats one of the issues I am having is that when they write these "A" they are not uniform. Ideally they would all be " A" or " B" but in the meantime I think I have been going about this all wrong. I am thinking it may be easier to find the " A" and " B" and "-A" "-B" and as it finds these then copy the row to a new sheet. Any suggestions or better ideas to do this is greatly appreciated.