How to delete Excel rows containing cells with less than specific amount of characters in them?

user_11611954 0
Viewed 303

This is what i want to do:

enter link description here

And I tried anwsers in this thread too ;-How do I delete a cell from Excel which contains less than 3 characters? I have only one column in sheet

But I can't get it to work.

I tried some codes like these after searching for a few hours but none of them worked or maybe I am not doing it correctly:

Code 1:

With ActiveSheet    
    lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
    For i = lastrow To 2 Step -1    
        If Len(.Cells(i, "N").Value) < 6 Then    
            .Rows(i).Delete
       End If
    Next i

Code 2:

Sub M_snb()
    [N1:N100] = [if(len(N1:N100)<6,"",N1:N100))]
    Columns(14).SpecialCells(4).EntireRow.Delete
End Sub
vba excel
2 Answers

Deleting rows from Excel is rather "expensive" task, thus it should be not done row-by-row, but all at once for saving time: 

Sub RemoveRows()

    Dim rowsToDelete    As Range
    Dim cnt             As Long
    Dim currentlastRow  As Long
    Dim ws              As Worksheet
    Dim myCell          As Range

    Set ws = Worksheets(1)
    currentlastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For cnt = 1 To currentlastRow
        Set myCell = ws.Cells(cnt, 1)
        If Len(myCell.Value2) < 6 Then
            If rowsToDelete Is Nothing Then
                Set rowsToDelete = myCell.EntireRow
            Else
                Set rowsToDelete = Union(rowsToDelete, myCell.EntireRow)
            End If
        End If
    Next cnt

    If Not rowsToDelete Is Nothing Then
        rowsToDelete.Delete
    End If

End Sub

Thus, in the code above, the rowsToDelete is the range with the cells, which are to be deleted. The deletion is carried out only once:

If Not rowsToDelete Is Nothing Then
    rowsToDelete.Delete
End If
user_54486260

This code will delete your rows in Column A:

Sub remove_rows()

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 2 Step -1

        If Len(Trim(.Range("A" & i).Value)) < 6 Then

            .Rows(i).EntireRow.Delete

       End If
    Next i

    End With

End Sub

Change A to any other column if you want.

user_57201440
Related