VBA Code to delete rows based on single criteria

Question

VBA Code to delete rows based on single criteria

Anonymous

I have data in columns A through AC, with some columns being blank. Column Headings are in row 1

I would like to delete rows if "Y" appears in column "X". Worksheet name is "List" and has about 3,000 lines, but varies day to day.

I've tried codes that loop but they take forever. I also have the following code, but it gives me an error message at the .SpecialCells(xlCellTypeConstants).EntireRow.Delete line. I then just kept the .EntireRow.Delete for that line and it still did not work. Any help would be greatly appreciated.

Dim UnusedColumn As Long, LastRow As Long

UnusedColumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Range("X2:X" & LastRow).Copy Cells(2, UnusedColumn)

With Columns(UnusedColumn)

.Replace "N", "", xlWhole

.SpecialCells(xlCellTypeConstants).EntireRow.Delete

.Clear

End With

Application.Calculation = xlCalculationAutomatic

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

Try this:

Sub DeleteRowsWithX()

Dim m As Long

With Worksheets("List")

.Range("X:X").AutoFilter Field:=1, Criteria1:="Y"

m = .Range("X" & .Rows.Count).End(xlUp).Row

If m > 1 Then

.Range("X2:X" & m).EntireRow.Delete

End If

.Range("X:X").AutoFilter

End With

End Sub

Answer 2

Anonymous

Worked great!! Thank you!

0 comments

Share via

VBA Code to delete rows based on single criteria

2 answers