VBA to remove entire row based on a specified list of values

wenyusa

Board Regular
Joined
Jan 16, 2009
Messages
50
So basically, I have a huge workbook. 187k+ rows. There are probably about 40k+ lines that could be removed from the sheet. However, they can only be removed based on a value in column M. This values determines whether it can either be kept or removed.

Currently there are 10 values in column M which should trigger the VBA to KEEP the row. If column M doesn't contain one of those values, then it can be deleted. If I have to hardcode the values that is ok for now, or I can put the values somewhere in the spreadsheet to reference. How do I write this? I'm struggling.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Sub deleteRows()

    For x = Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
        Select Case Cells(x, 13)
        
        Case "Value 1", "Value 2", "Value 3", "Value 4", _
                    "Value 5", "Value 6", "Value 7", _
                        "Value 8", "Value 9", "Value 10"
            'KEEP THE ROWS
        Case Else
            'NUKE THE ROWS
            Cells(x, 1).EntireRow.Delete
        End Select
    Next x
End Sub

This assumes strings. For numbers, remove the "".
 

wenyusa

Board Regular
Joined
Jan 16, 2009
Messages
50
It looks like this works perfect...

It is working from the bottom up?

How do you propose a way so I don't have to hard code the values? Is there a way that I can reference a range of cells on Sheet2?
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
It looks like this works perfect...

It is working from the bottom up?

How do you propose a way so I don't have to hard code the values? Is there a way that I can reference a range of cells on Sheet2?

Code:
Sub deleteRows()

    Dim list As Worksheet
    Dim data As Worksheet
    
    Set data = Sheets("Sheet1") 'CHANGE ME (This is where data will be deleted)
    Set list = Sheets("Sheet2") 'CHANGE ME (This is where the list of values to keep is in column A)
    


    With sheet2
        For x = list.Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
            Select Case list.Cells(x, 13)
            
            Case .Cells(1, 1), .Cells(2, 1), .Cells(3, 1), .Cells(4, 1), .Cells(5, 1), _
                    .Cells(6, 1), .Cells(7, 1), .Cells(8, 1), .Cells(9, 1), .Cells(10, 1)
                'KEEP THE ROWS
            Case Else
                'NUKE THE ROWS
                list.Cells(x, 1).EntireRow.Delete
            End Select
        Next x
    End With
End Sub

It is working from the bottom up because if you delete rows from the top down in a loop, it will skip rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,363
Messages
5,528,263
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top