Help with a macro to delete rows

[jscranton]

Haven't seen this elsewhere. My intent is to create a Macro to delete rows unless the row is a "master" row. Master Rows are easily identifiable because the value in Column A is always 7 characters.

I thought this would work. ANy suggestions?


Sub DeleteRow()

' Disables protected sheet
ActiveSheet.Protect Password:="password", UserInterFaceOnly:=True

Dim X As String

X = ActiveCell.Offset(1, 0).Range(A.1).Select


If Len(X) = 7 Then GoTo NoDeleteRow:
With ActiveCell
.EntireRow.Copy
Selection.Delete Shift:=xlUp

End With

End If


NoDeleteRow:
warning = MsgBox("This Row Can Not Be Deleted")

End Sub

 

[jeffreybrown]

Try...

Sub DeleteRow()
    Dim x As Long
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For x = LR To 2 Step -1
        If Len(Range("A" & x).Value) = 7 Then
            MsgBox ("This Row Can Not Be Deleted")
        Else
            Range("A" & x).EntireRow.Delete Shift:=xlUp
        End If
    Next x
End Sub

 

[jscranton]

Tried your code and it never stops running. The Message Box stays up until I force close Excel.

Ideas?

 

[jeffreybrown]

If you are looking to keep any row which has a Length of 7 in column A, why even use the message box at all. At least to me, having to click OK on the message box would get extremely annoying.

 

[jscranton]

The main issue is that the worksheet is protected so users can't delete rows.

I wanted to create a macro buttons which allowed them to delete "bad" rows but not rows that must remain.

 

[jeffreybrown]

Just unprotect the sheet at the beggining of the code and then reprotect it before exiting the sub.

Sub DeleteRow()
    Dim x As Long
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Unprotect Password:="password"
    For x = LR To 1 Step -1
        If Len(Range("A" & x).Value) <> 7 Then
            Range("A" & x).EntireRow.Delete Shift:=xlUp
        End If
    Next x
    ActiveSheet.Protect Password:="password"
End Sub