Solved

Debug Help on Simple Excel VBA To Clear Range

Posted on 2015-02-21
38
108 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now