CODEWITHSUNDEEP
excelvba
Tejinder Mangat
Tejinder Mangat

Reputation: 53

Delete Rows Based on Cell Values

I want to delete any row in a data table with a cell value in column B of "3DO" or "DOS" and shift remaining cells up.

Screenshot of data table
enter image description here

Workbook Name: StackOverflowTest.xlsm

Worksheet Name: Sheet1

Cell range: A1:J100000

Column with value: B

Values to search for: "3DO", "DOS"

My understanding is that the following VBA code could be altered to achieve this:

Sub Delete_Rows_Based_On_Value()
'Apply a filter to a Range and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/


Dim ws As Worksheet

  'Set reference to the sheet in the workbook.
  Set ws = ThisWorkbook.Worksheets("Regular Range")
  ws.Activate 'not required but allows user to view sheet if warning message appears

  'Clear any existing filters
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

  '1. Apply Filter
  ws.Range("B3:G1000").AutoFilter Field:=4, Criteria1:=""

  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True

  '3. Clear Filter
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0

End Sub

UPDATE:

This code from @Harun24HR seems to do the trick for me

Sub DelVisible()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("RAW")

    ws.Range("$A$1:$J$100000").AutoFilter Field:=2, Criteria1:="=3DO", _
        Operator:=xlOr, Criteria2:="=DOS"

    ws.Range("$A$2:$J$100000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.Range("$A$1:$J$100000").AutoFilter

End Sub

But now I'd like to add more criteria to search for in this statement and I'm unclear on how to do that. Say I am looking for "3DO", "DOS", and "Mac" for example, how would I add the "Mac" criteria in addition to this statement?

Update #2

Thanks to @user11053804 I have a working solution for looking for 3+ criteria

Sub RemoveOldPlatforms()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("RAW")

    ws.Range("$A$1:$J$100000").AutoFilter Field:=2, Criteria1:=Array("3DO", "All Other", "DOS", "Mac"), Operator:=xlFilterValues

    ws.Range("$A$2:$J$100000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.Range("$A$1:$J$100000").AutoFilter

End Sub

Upvotes: 0

Views: 1129

Answers (2)

user11053804
user11053804

Reputation:

Your AutoFilter statement is inadequate.

ws.Range("B3:B1000").AutoFilter Field:=1, Criteria1:="EDO", Criteria2:="DOS", operator:=xlOr

Additional criteria would require an array with xlFilterValues.

ws.Range("B3:B1000").AutoFilter Field:=1, Criteria1:=array("EDO", "DOS", "All Others"), operator:=xlFilterValues

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36780

What about following codes? Can you please have a try and feedback us.

Sub DelVisible()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Regular Range")

    ws.Range("$A$1:$J$10000").AutoFilter Field:=2, Criteria1:="=3DO", _
        Operator:=xlOr, Criteria2:="=DOS"

    ws.Range("$A$2:$J$10000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.Range("$A$1:$J$10000").AutoFilter

End Sub

Upvotes: 1

Related Questions