I searched the forum but wasnt able to find anything that really worked. I have a simple sheet where i want to filter column AZ for anything labeled "LOW" or "TBD" and delete only those. Then remove the filter.
I was running the below code but since it deleted each one in line it would miss some as the i would just keep going.
For i = 2 To access.UsedRange.Rows.Count
If access.Cells(i, 11) = "LOW" Or "TBD" Then
Rows(i).delete
Exit For
End If
Next i
If access.Cells(i, 11) = "LOW" Or access.Cells(i, 11) = "TBD" Then
or if you want to be a bit more fancier:
With access.Cells(i, 11)
If .Value2 = "LOW" Or .Value2 = "TBD" Then Rows(i).Delete
End With
Whenever you are deleting rows, this is the best practice:
For i = access.UsedRange.Rows.Count to 2 Step -1
If it is a bit slow, consider adding the rows to a range with Union() and deleting the whole range. The Union() should be the fastest solution I guess - How do I ignore the first row in an Excel macro that deletes all rows based on a criteria?
For i = access.UsedRange.Rows.Count to 2 Step -1, @JoshHudsonWhen ever you are deleting records in a range in Excel and you referencing the rows using the row number you should start at the bottom and work your way to the top. The reason is because when you're examine row 5 and you remove it, row 6 now becomes row 5 but your loop counter increments to row 7 - thus you never examine row 6.
I would suggest:
Dim LastRow as Long
LastRow = access.UsedRange.Rows.Count
For i = LastRow To 2 Step - 1
If access.Cells(i, 11) = "LOW" Or "TBD" Then
Rows(i).delete
End If
Next i
For line should be For i = LastRow To 2 Step -1 to tell it to decrease the count by 1 each timeToy can try this way. And why you add 11 for column index. AZ is index 50
Sub test2()
' Active workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim i As Long
'*******************************************
'Adapt this vars
'define your sheets
Dim ws_1 As Worksheet
Set ws_1 = wb.Sheets("Feuil1")
'definie the last Rows
Dim lastRow_ws1 As Long
lastRow_ws1 = ws_1.Range("AZ" & Rows.Count).End(xlUp).Row
'*******************************************
For i = lastRow_ws1 To 2 Step -1
Dim keySearch As String
keySearch = ws_1.Cells(i, 50).Value
If keySearch = "LOW" Or keySearch = "TBD" Then
ws_1.Rows(i).EntireRow.Delete
End If
Next i
End Sub
424 to about 99.9% of the users in StackOverflow.Here is a version that uses AutoFilter instead of running through the rows 1-by-1. As a warning, this will clear any filters that you have on the data. (Unless you have macros to save/restore filters)
Const FilterColumn = 52 ' Column AZ
'Clear Filters
access.AutoFilterMode = False
'Filter for data
access.UsedRange.AutoFilter FilterColumn, "LOW", xlOr, "TBD"
'If Data exists
If access.Cells(access.Rows.Count, FilterColumn).End(xlUp).Row > 1 Then
'Delete visible rows
access.Range(access.Cells(2, FilterColumn), access.Cells(access.Rows.Count, FilterColumn)).EntireRow.Delete
End If
'Clear Filters
access.AutoFilterMode = False