VBA code to delete rows specified by user

[njf81]

Hi
Quite new to VBA, but having trouble with a macro. I have a protected spreadsheet where users need to delete rows they do not require when they use it.

I therefore need a macro which will do the following when they click a button:

Ask the user which rows they wish to delete (this needs to be able to include a range of rows e.g. 4-10, not just single rows)
Deletes those rows


OR option B
the user selects the rows
They click a delete rows button
selected rows are coloured yellow
Message box (Yes/No) asks if the user has highlighted the correct rows
If yes go to next prompt
If No, cancel macro

2nd message box
Message box (Yes/No) – do you wish to delete the selected rows
If no: stop Macro
If Yes, delete entire rows


Any help would be extremely appreciated!

NJF

 

[HalfAce]

Hi.
Here are a couple ideas you can try.
#1.

Ask the user which rows they wish to delete (this needs to be able to include a range of rows e.g. 4-10, not just single rows)
Deletes those rows

Sub EnterRowsToDelete()
TheseRows = Application.InputBox("Which row(s) would you like to delete?" & vbNewLine & _
            "Please note that multiple row enteries must be one contiguous range and must be entered in the format of '5:10' or no rows will be deleted", "Rows To Delete")
On Error Resume Next
If Len(TheseRows) > 0 Then Rows(TheseRows).EntireRow.Delete: Exit Sub
If Error.Number <> 0 Then On Error GoTo 0
End Sub

And #2. (Option B?)

Sub DeleteSelectedRows()
Dim c As Range, DltRng As Range
For Each c In Selection
    If DltRng Is Nothing Then
        Set DltRng = c
      Else
        Set DltRng = Union(c, DltRng)
    End If
Next c
If Not DltRng Is Nothing Then DltRng.EntireRow.Interior.ColorIndex = 6
Rply = MsgBox("Are the highlighted rows the ones you want to delete?", vbYesNo)
If Rply = vbYes Then
    DltRng.EntireRow.Delete
    ActiveCell.Select
Else
    DltRng.EntireRow.Interior.ColorIndex = xlNone
    MsgBox "No rows were deleted."
End If
End Sub

Note, this assumes you have no color formatting in the selected cells, so it removes the formatting we installed if the user chooses No. If you do have background color formatting that you want to return to then we'll need to know what that is and amend this line of code:
DltRng.EntireRow.Interior.ColorIndex = xlNone
to whatever formatting you want to use.


Hope it helps.

 

[HalfAce]

Oh, my bad. I overlooked the sheet being protected. Try these routines instead of the ones posted above.

Sub DeleteSelectedRows()
ActiveSheet.Unprotect "Your Password Here If Any"
Dim c As Range, DltRng As Range
For Each c In Selection
    If DltRng Is Nothing Then
        Set DltRng = c
      Else
        Set DltRng = Union(c, DltRng)
    End If
Next c
If Not DltRng Is Nothing Then DltRng.EntireRow.Interior.ColorIndex = 6
Rply = MsgBox("Are the highlighted rows the ones you want to delete?", vbYesNo)
If Rply = vbYes Then
    DltRng.EntireRow.Delete
    ActiveCell.Select
Else
    DltRng.EntireRow.Interior.ColorIndex = xlNone
    MsgBox "No rows were deleted."
End If
ActiveSheet.Protect "Your Password Here If Any"
End Sub

Or...

Sub EnterRowsToDelete()
ActiveSheet.Unprotect "Your Password Here If Any"
TheseRows = Application.InputBox("Which row(s) would you like to delete?" & vbNewLine & _
            "Please note that multiple row enteries must be one contiguous range and must be entered in the format of '5:10' or no rows will be deleted", "Rows To Delete")
On Error Resume Next
If Len(TheseRows) > 0 Then Rows(TheseRows).EntireRow.Delete:
If Error.Number <> 0 Then On Error GoTo 0
ActiveSheet.Protect "Your Password Here If Any"
End Sub

If you don't use a password then simply use the lines:
ActiveSheet.Unprotect and ActiveSheet.Protect instead.

Hope it helps.

 

[njf81]

Thanks very much. I'm loving the highlighting the rows option.

However, there is background colour already in existence (only on some cells in that row), and so if the user chooses not to delete the selected rows, I need it to revert to the existing colours.

Also, how to change it from yellow highlighting to red?

Thanks for your assistance, much appreciated.

 

[HalfAce]

Restoring your original background colors could be simple or quite involved. All depends on what you have.
What cells do you want to turn back red? (Any other colors that will need to be restored also? - where might those be?)

The code syntax for turning the background color of a range red would look like so:
Range("YourRangeHere").Interior.ColorIndex = 3

That help at all?