Conditional deletion in VBA

advertisements

I am trying to piece together code to make my macro work correctly. This approach has served me well in the past but I cannot seem to adapt any code correctly.

I found the following

Sub way()

Dim Cell As Range
For Each Cell In Range("A1").CurrentRegion
    If Len(Cell) < 2 Then Cell.EntireRow.Delete
Next Cell

End Sub

I can adapt the If Len(Cell) criteria to my liking. For example = 10

I do not know how to adapt the code to make it search through all cells in column A and delete the appropriate rows. The code above only does it for A1.

Ideally I would like to delete all rows with cells in column A that have a length of 10 characters. Or alternatively, with a completely different set of code, delete all other rows that do not contain cells in column A that have a length of 10 characters.


When deleting rows it is best to loop backwards:

Sub way()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long

Set ws = ActiveSheet
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
For i = lastrow To 1 Step -1
    If Len(ws.Cells(i, 1)) < 2 Then ws.Rows(i).Delete
Next i

End Sub