Need help making a macro that deletes cells with more than 7 characters [closed]

Closed. This question needs to be more focused. It is not currently accepting answers. 开发者_StackOverflow

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 6 years ago.

Improve this question

I'm new to Excel and I'm having a hard time trying to find a macro to delete a row if a cell contains more than 7 characters. I know I'm supposed to use =len(a1) to get the length, but the language is really archaic to me.

If someone can make me the macro, I'd really appreciate it.

---

Sub test2()
    Dim last As Long, i As Long
    'make sure it works with any sheet size
    last = Cells(Rows.Count, 1).End(xlUp).Row
    'loop bottom to top !
    For i = last To 1 Step -1
        If Len(Cells(i, 1)) > 7 Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
End Sub

---

Try this assuming the data is in column A:

Sub DeleteRows()

    Dim r As Integer

    Let r = Range("A65536").End(xlUp).Row
    Application.ScreenUpdating = False
    Do Until r = 0
        If Len(Cells(r, 1)) > 7 Then Cells(r, 1).EntireRow.Delete
        Let r = r - 1
    Loop
    Let r = Empty
    Application.ScreenUpdating = True

End Sub    

---

Just for fun I thought I would give you another solution. I tried it and it works.

Sub DeleteAbove7()

    Dim b1 As Boolean
    Dim i As Integer
    Dim r1 As Range
    Dim v1 As Variant

    v1 = Application.Transpose(ActiveSheet.Range("A1:A" _
         & Cells(Application.Rows.Count, 1).End(xlUp).Row).Value2)

    b1 = False
    For i = LBound(v1) To UBound(v1)
        If Len(v1(i)) > 7 Then
            If b1 Then
                Set r1 = Union(r1, Cells(i, 1))
            Else
                Set r1 = Cells(i, 1)
                b1 = True
            End If
        End If
    Next i

    r1.EntireRow.Delete

    'ActiveSheet.Range("B1:B" & UBound(v1)) = Application.Transpose(v1)

    Set r1 = Nothing
    Set v1 = Nothing

End Sub