Excel VBA to Delete Unwanted Data

[jluangrath88]

Hi fellow Excel experts!

I need a bit of help figuring this one out. I need a VBA code to delete some columns and rows that have unwanted data.

Let’s call this sheet, Sheet1. Sheet1 has Columns A to L. However, I only need Column A, B,C,D,H, and J. I want to completely delete Columns E, F, G, I, K, and L.

As for the rows, Sheet1 has an infinite number of rows. I need to delete any row that does not have the value of “X” in Column J.

Can someone provide me with some help to get started? Thanks a bunch.

 

[Trebor76]

Hi jluaugrath88,

This should do the job (just run it while on the sheet in question):

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(J" & lngStartRow & "=""X"",NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    'Delete columns E, F, G, I, K, and L
    Range("E:G,I:I,K:L").EntireColumn.Delete
        
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. J that had a ""X"" in them have now been deleted as well as columns E, F, G, I, K, and L.", vbInformation

End Sub

Note I delete the rows first or else Col. J will be shifted when the columns are subsequently deleted.

Please initially try this on a copy of your data as the results cannot be undone if they're not as expected.

Sheet1 has an infinite number of rows

No, assuming you're on Excel 2007 or later there are 1,048,576 rows every each tab.

Regards,

Robert

 

[jluangrath88]

Hi jluaugrath88,

This should do the job (just run it while on the sheet in question):

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(J" & lngStartRow & "=""X"",NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    'Delete columns E, F, G, I, K, and L
    Range("E:G,I:I,K:L").EntireColumn.Delete
        
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. J that had a ""X"" in them have now been deleted as well as columns E, F, G, I, K, and L.", vbInformation

End Sub

Note I delete the rows first or else Col. J will be shifted when the columns are subsequently deleted.

Please initially try this on a copy of your data as the results cannot be undone if they're not as expected.



No, assuming you're on Excel 2007 or later there are 1,048,576 rows every each tab.

Regards,

Robert

Hi Robert,

This actually did the opposite of what I wanted. It removed the ones with the X's. I wanted to keep the ones with the X.

 

[jluangrath88]

Hi jluaugrath88,

This should do the job (just run it while on the sheet in question):

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(J" & lngStartRow & "=""X"",NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    'Delete columns E, F, G, I, K, and L
    Range("E:G,I:I,K:L").EntireColumn.Delete
        
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. J that had a ""X"" in them have now been deleted as well as columns E, F, G, I, K, and L.", vbInformation

End Sub

Note I delete the rows first or else Col. J will be shifted when the columns are subsequently deleted.

Please initially try this on a copy of your data as the results cannot be undone if they're not as expected.



No, assuming you're on Excel 2007 or later there are 1,048,576 rows every each tab.

Regards,

Robert

I adjusted the IF formula a bit to suite my needs. Instead of using IF(J" & lngStartRow & "=""X"",NA(),"""")" ... I switched around the last bit to IF(J" & lngStartRow & "=""X"","""", NA())".

Worked like a charm. Thanks.

 

[Trebor76]

Worked like a charm. Thanks.

NP. I'm glad we got there in the end

 

[jluangrath88]

On the same subject of this code. How would I be able to rewrite the IF statement and change it to an IF-AND statement. For example... If Column H = X and Column J = X, then keep the rows. However if both Column H AND Column J do not contain the value of X then delete the entire row.

 

[jluangrath88]

On the same subject of this code. How would I be able to rewrite the IF statement and change it to an IF-AND statement. For example... If Column H = X and Column J = X, then keep the rows. However if both Column H AND Column J do not contain the value of X then delete the entire row.

Never mind. Figured it out lol.

For those wondering the same thing as me, this is what I did:

<code>

Sub DeleteUnwantedRows()
    With ThisWorkbook.Sheets("Sheet1")
        Dim i As Long
        Dim LR As Long: LR = .Cells(Rows.Count, "A").End(xlUp).Row
        
        For i = LR To 2 Step -1
            If Not (.Cells(i, "H") = "X" And .Cells(i, "J") = "X") Then .Cells(i, "A").EntireRow.Delete
        Next i
    End With
End Sub

</code>