Solved

Debug Help on Simple Excel VBA To Clear Range

Posted on 2015-02-21
38
116 Views
Last Modified: 2016-02-10
Using:  Excel 2010

The VBA script below generates this error:
----------  Start of Error ------
Microsoft Visual Basic

Runtime error '1004':

Application-defined or object-defined error
-----------  End of Error ------

The culprit line in the script is:

ActiveSheet.Range("RangeToClear").ClearContents

I've also tried it with just:

Range("RangeToClear").ClearContents

but then I get the same Runtime error number but with:

Method 'Range' of object '_Global' failed

I suspect it's because I haven't properly defined something or targeted the active sheet properly.

I have the Sub below, under VBAProject (MyWorkbook.xlsm) > Modules > 

Thoughts on how to fix?


----- Script Below -----
Sub sbClearRangeOfData()
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
     ActiveSheet.Range("RangeToClear").ClearContents
     Else
     MsgBox ("Aborting Range Clearing")
  End If
End Sub
0
Comment
Question by:qeng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 23
  • 15
38 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623612
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

Open in new window

0
 

Author Comment

by:qeng
ID: 40623633
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?
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623640
To clear selected cells, you can simply do:
Selection.Clear

Open in new window

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623644
I think i did not understand what you were saying by MyNamedRange.

What exactly you would like to do?
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623650
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

Open in new window

0
 

Author Comment

by:qeng
ID: 40623658
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")
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623666
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

Open in new window

0
 

Author Comment

by:qeng
ID: 40623681
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
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623692
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

Open in new window

0
 

Author Comment

by:qeng
ID: 40623721
:) 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)
0
 

Author Comment

by:qeng
ID: 40623722
Thanks for your help Jean-Marc, sorry if my description wasn't clear earlier.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623726
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

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623728
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

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623734
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"

Open in new window

0
 

Author Comment

by:qeng
ID: 40623742
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
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623746
what represent   Set MyRange = Worksheets("Sheet1").Range("MyNamedRange")?

You need to define MyNamedRange
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623771
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"

Open in new window

0
 

Author Comment

by:qeng
ID: 40623772
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.
0
 

Author Comment

by:qeng
ID: 40623773
Sorry, I was posting my message while your last one was coming in.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623778
I have replicated in this Excel file.
Delete-range.xlsm
0
 

Author Comment

by:qeng
ID: 40623808
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
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623812
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"

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623816
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"

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40623833
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"

Open in new window

0
 

Author Comment

by:qeng
ID: 40624442
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.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624490
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?
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624494
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
0
 

Author Comment

by:qeng
ID: 40624496
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.
0
 

Author Comment

by:qeng
ID: 40624502
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.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624508
Can you try this one? Just tested it and it works.
Delete-selected-cells-no-3.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624549
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
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624572
This one i hope you will like.  :-)
Delete-selected-cells-no-5.xlsm
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 40624591
If you now use this one, it will use the dot borders to identify all cells in all cases.

Let me know what you think.
Delete-selected-cells-no-6.xlsm
0
 

Author Comment

by:qeng
ID: 40665617
Ahhh very slick Jean-Marc.  Perfect.  Thank you so much.
0
 

Author Closing Comment

by:qeng
ID: 40665620
Super help and dedication from Jean-Marc.  Thank you again.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40665627
I'm happy i was able to help
0
 

Author Comment

by:qeng
ID: 40665709
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
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40665729
sure. i will be able to help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question