Debug Help on Simple Excel VBA To Clear Range

HI qeng

Try like this.

Dim RangeToClear As Range
Set RangeToClear = Application.InputBox("Select a range", "Get Range", Type:=8)
userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
If userInput = vbYes Then
     RangeToClear.ClearContents
     Else
     MsgBox ("Aborting Range Clearing")
  End If

Select allOpen in new window

Thx Jean-Marc, I see my mistake.

How would I modify this code to clear a named range (say MyNamedRange), instead of the user having to select it?

To clear selected cells, you can simply do:

Selection.Clear

Select allOpen in new window

I think i did not understand what you were saying by MyNamedRange.

What exactly you would like to do?

Are you looking at something like this?

Private Sub CommandButton21_Click()
userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
If userInput = vbYes Then
     Range("A1:D10").ClearContents
     Else
     MsgBox ("Aborting Range Clearing")
    End If
End Sub

Select allOpen in new window

I'm wanting to code something like this (conceptually; of course I know the syntax is garbage):

MyNamedRange = A1:D10
on button click, show MyNamedRange (marching ants)
display messagebox = "Are you sure you want to delete the data in this range", vbYesNo
if Yes > delete data in MyNamedRange (leaving formatting intact)
if No > display messagebox = "Aborting Range Clearing")

Is this what you are looking for?

Set RangeToClear = Range("A1:D10")

userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
If userInput = vbYes Then
     RangeToClear.ClearContents
     Else
     MsgBox ("Aborting Range Clearing")
End If

Select allOpen in new window

Getting close :),

But two problems:

1.  It's not showing the user what the range is that is about to be deleted
           (from above:  "on button click, show MyNamedRange (marching ants)" ie show target range for deletion)

2.  It's clearing the formatting of the cells as well as the data.  I need to leave the formatting of the cells intact
           (from above:  "if Yes > delete data in MyNamedRange (leaving formatting intact")

        I was able to fix this issue by changing:
                                          RangeToClear.Clear
        to:
                                          RangeToClear.ClearContents

If you want to tell the user what range is about to be deleted, you can update the message like bellow:

Set RangeToClear = Range("A1:D10")

userInput = MsgBox("Are you sure you want to delete Range A1 to D10?", vbYesNo)
If userInput = vbYes Then
     RangeToClear.ClearContents
     Else
     MsgBox ("Aborting Range Clearing")
End If

Select allOpen in new window

:) but the range will change in use, as for example when users add items to a table

(I was just using A1:D10 in here as a test range; the real ranges I'm working with in my workbooks are multiple tables all over the place; I'm programming some buttons to clear certain calculated ranges based on certain scenarios).

That's why I want to be able to visually outline the targeted named range is in these types of VBA macros (such as select a named range and then leave the selection visible to the user while he is served the message about proceeding with the deletion).

Otherwise, the user is asked if the macro should delete the range but he has no idea what the range is (I want to visibly show the perimeter of the range, just like when someone drags his mouse over a range, in case something went wrong with the code or someone or something messed up the spreadsheet design)

Thanks for your help Jean-Marc, sorry if my description wasn't clear earlier.

Let me know if this is a better approach. If not, i can  take a look tomorrow.

Dim Response As Integer
Range("A1:D10").Select

Response = MsgBox("Are you sure you want to delete Range ""A1:D10"" ?", vbYesNoCancel + vbQuestion, "Exit?")
Select Case Response
	Case vbYes
	Set RangeToClear = Range("A1:D10")
	RangeToClear.ClearContents

	Case vbNo

	Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
	userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
	If userInput = vbYes Then
		RangeToClear.ClearContents
	Else
		MsgBox ("Aborting Range Clearing")
		Exit Sub
	End If

	Case vbCancel
	MsgBox "You pressed Cancel"
End Select

Select allOpen in new window

Just fixed something on the code:

Dim Response As Integer
Range("A1:D10").Select

Response = MsgBox("Are you sure you want to delete Range ""A1:D10"" ?", vbYesNoCancel + vbQuestion, "Exit?")
Select Case Response
	Case vbYes
	Set RangeToClear = Range("A1:D10")
	RangeToClear.ClearContents

	Case vbNo
	Cells(1, 1).Select
	Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
	userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
	If userInput = vbYes Then

		RangeToClear.ClearContents
	Else
		MsgBox ("Aborting Range Clearing")
		Exit Sub
	End If

	Case vbCancel
	MsgBox "You pressed Cancel"
End Select

Select allOpen in new window

This would remove the error if a user press ESC during the process.

Dim Response As Integer
Range("A1:D10").Select
On Error GoTo handler

Response = MsgBox("Are you sure you want to delete Range ""A1:D10"" ?", vbYesNoCancel + vbQuestion, "Exit?")
Select Case Response
    Case vbYes
    Set RangeToClear = Range("A1:D10")
    RangeToClear.ClearContents

    Case vbNo
    Cells(1, 1).Select
    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then

        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    MsgBox "You pressed Cancel"
End Select
Exit Sub

handler:
MsgBox "You pressed Cancel"

Select allOpen in new window

Thx Jean-Marc.

I'm trying to tweak your second to last code to get it to do what I need with named ranges but my code isn't correct.  I'm getting a Type Mismatch when I run it.



Sub Deleter()
  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")
Dim Response As Integer
MyRange.Select

Response = MsgBox("Are you sure you want to delete Range " & MyRange & " ?", vbYesNoCancel + vbQuestion, "Exit?")
Select Case Response
    Case vbYes
    Set RangeToClear = MyRange
    RangeToClear.ClearContents

    Case vbNo

    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then
        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    MsgBox "You pressed Cancel"
End Select
End Sub

what represent   Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")?

You need to define MyNamedRange

Ok, i think i understand what you are trying to do.

Try like this:

Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")
Dim Response As Integer
MyRange.Select


Response = MsgBox("Are you sure you want to delete Range " & MyRange.Address & " ?", vbYesNoCancel + vbQuestion, "Exit?")
On Error GoTo handler
Select Case Response
    Case vbYes
    Set RangeToClear = MyRange
    RangeToClear.ClearContents

    Case vbNo

    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then
        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    MsgBox "You pressed Cancel"
End Select

Exit Sub

handler:
MsgBox "You pressed Cancel"

Select allOpen in new window

OK,

In the meantime, I managed to get a named ranged to display correctly, like so: (in my worksheet i defined MyNamedRange = "X1:Y10" )

------------------
Sub MyRangeAddress()

Dim MyRange As Range
Dim MyRangeAddress As String
 
Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")

MyRangeAddress = MyRange.Parent.Name & "!" & MyRange.Address(External:=False)
MsgBox "range is " & MyRangeAddress
End Sub
-------------------

Now I just need to integrate this type of named range approach into your previous script.

Sorry, I was posting my message while your last one was coming in.

I have replicated in this Excel file.
Delete-range.xlsm

We are close Jean-Marc :),

Just on the 'No' option, the selected range disappears and the original range stays showing as 'selected' all the way through the script.

Please see attached image.

Thx
Deletion-on-No-Does-Not-Display-Correctl

This will do it:

Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")
Dim Response As Integer
MyRange.Select


Response = MsgBox("Are you sure you want to delete Range " & MyRange.Address & " ?", vbYesNoCancel + vbQuestion, "Exit?")
On Error GoTo handler
Select Case Response
    Case vbYes
    Set RangeToClear = MyRange
    RangeToClear.ClearContents

    Case vbNo
   Range("X1").Select
  Application.CutCopyMode = False
    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then
        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    MsgBox "You pressed Cancel"
End Select

Exit Sub

handler:
MsgBox "You pressed Cancel"

Select allOpen in new window

Do this instead: :)

Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")
Dim Response As Integer
MyRange.Select


Response = MsgBox("Are you sure you want to delete Range " & MyRange.Address & " ?", vbYesNoCancel + vbQuestion, "Exit?")
On Error GoTo handler
Select Case Response
    Case vbYes
    Set RangeToClear = MyRange
    RangeToClear.ClearContents
    Range("X1").Select
    Application.CutCopyMode = False

    Case vbNo
   Range("X1").Select
   Application.CutCopyMode = False
    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then
        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    MsgBox "You pressed Cancel"
End Select

Exit Sub

handler:
MsgBox "You pressed Cancel"

Select allOpen in new window

This can also be better.

It will unselected Range as soon you cancel also.

Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")
Dim Response As Integer
MyRange.Select

Response = MsgBox("Are you sure you want to delete Range " & MyRange.Address & " ?", vbYesNoCancel + vbQuestion, "Exit?")
On Error GoTo handler
Select Case Response
    Case vbYes
    Set RangeToClear = MyRange
    RangeToClear.ClearContents
    Range("X1").Select
    Application.CutCopyMode = False

    Case vbNo
   Range("X1").Select
   Application.CutCopyMode = False
    Set RangeToClear = Application.InputBox("Select a range", "", Type:=8)
    userInput = MsgBox("Are you sure you want to delete the data in this range", vbYesNo)
    If userInput = vbYes Then
        RangeToClear.ClearContents
    Else
        MsgBox ("Aborting Range Clearing")
        Exit Sub
    End If

    Case vbCancel
    Range("X1").Select
    MsgBox "You pressed Cancel"
End Select

Exit Sub

handler:
Range("X1").Select
MsgBox "You pressed Cancel"

Select allOpen in new window

Merci Jean-Marc,

Using a pre-defined location like X1 to send the cursor to isn't a good fix for my situation because it causes the screen to jump away from where the action was, to an area (I was using MyNamedRange = X1:X10 simply as an example).

In this application, we can assume that MyNamedRange could be anywhere on the worksheet or on any of the worksheets in the workbook.

What I'm working towards is a 'range clearer' which I can apply to different macro buttons, each designed to clear a different range in the workbook but I have to get the basic clear functionality working first.

I've been trying to modify the last code you gave me to jump to the active cell and select that one instead of jumping to X1, or to define a 'cursor parking spot' in a named range and to go to that cursor parking spot as a means of getting the selection out from the original MyNamedRange.

I was googling this issue and there are quite a few posts from people trying to 'unselect' ranges like I'm trying to do.

I'll keep at it a bit longer and post where I've gotten to.  I'm sure I'll need your help.

So just as a recap,  if i understand the only issue we need to fix, we just need to Unselect MyNamedRange only instead of changing position on the Excel file due to the Range("X1").Select. Correct?

Everything else is good?

If this is the case, have a look at this one. I tweaked the code do work the way you want it.
Delete-selected-cells-no-3.xlsm

Essentially yes, except that on the 'No' option, after selecting the new desired range, the range selection becomes no longer visible when we ask the user, 'Are you sure you want to delete this range' ... even though the range is selected in the background and the correct range then gets deleted, there are no selections visible on the worksheet.

Jean-Marc, I didn't have any success with the last code.

Clicking the Delete Cells button did nothing.
Preselecting the cells and clicking the Delete Cells button also didn't do anything.

We were close before.  It's just that:

a)  The No and Cancel cases were jumping to areas of a worksheet that were out of view and may not be appropriate on all worksheets (instead of just 'deselecting' the initial range).

b)  When the user was selecting a different range than the initial one, after selecting his range, it would disappear between the selection process and the message, 'are you sure you want to delete this range' (so the user would have to answer 'yes' but would be blind as to which range he was agreeing to delete at that point.

Can you try this one? Just tested it and it works.
Delete-selected-cells-no-3.xlsm

This can probably be a good work around also.

Let me know what you think.

We can also replace the green cells with cell dots borders to mimic the selection. Once delete or if you cancel the process, it will return to normal borders.

What do you think?
Delete-selected-cells-no-4.xlsm

This one i hope you will like.  :-)
Delete-selected-cells-no-5.xlsm

Ahhh very slick Jean-Marc.  Perfect.  Thank you so much.

Super help and dedication from Jean-Marc.  Thank you again.

I'm happy i was able to help

You most certainly did.  Thanks a lot.  My delay in getting back was in no way related to a lack of interest on my part.  This was perfect.

I'm not sure if you are as expert in Excel Pivot Tables (I'm a complete beginner) but I'm about to post a question about how to structure a Pivot Table.  I'll attach it here just in case :)
Pivot-Table-Doc-by-Due-Date.jpg

sure. i will be able to help