Help with a macro to delete rows

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Try...

Code:
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

Well-known Member
Joined
May 30, 2011
Messages
707
Tried your code and it never stops running. The Message Box stays up until I force close Excel.

Ideas?
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
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

Well-known Member
Joined
May 30, 2011
Messages
707
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

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Just unprotect the sheet at the beggining of the code and then reprotect it before exiting the sub.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,907
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top