MACRO: VBA TO RUN CODE ONLY ON USER SELECTED RANGE (w/ error handling)

[cgeorge4]

Hello,
I have exhausted myself trying to modify this code to run ONLY in the range the user selects. I have also tried to add an error handling section with a message box in case the user selects a range that does not contain any hard-coded cells.

1) Excel 2010 / PC Windows Premium 7

2) Code needed to delete a hard-coded number in any range selected by the user - BUT not making any changes to cells with formulas within the same range selected by the user.

3) An error handling section will be needed in case the user selects a range that doesn't have any hard-coded cells.


4) An error handling message box will be needed for #3. Message: "There are no hardcoded cells in the range you have selected. Please select a range".

5) The range selected by the user will normally be whole rows - but I would like the code to work for rows selected, or columns selected, or a range of cells selected.

IMPORTANT NOTE!!!: The rows selected can only be from row 37 and down. It will be an error by the user if they select rows 1 thru 36.
This code can not run on rows 1 thru 36. An error handling message will be needed in case the user tries to run this code
while rows 1 thru 36 (or any cells in those rows) are selected.



The code that I have modified works but not completely. It does delete the hard-coded cells in the range that I select - but if I have my cursor on one empty cell - it will remove hard-coded data from the WHOLE sheet.

I appreciate any assistance.

Here is my code:

Sub DELETE_Hardcode_In_Selected_Range()


With Selection


Selection.Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents
End With


End Sub




Thank you so much,
Juicy,

 

[Steve_R]

Hi Juicy

You can prompt the user to select a range via InputBox

     Set myRng = Application.InputBox( _
                prompt:="Select a cell or range or cells", Type:=8)
    Debug.Print myRng.Address 'remove when you are happy
    
    '{your code}

'EXAMPLE
'With myRng
'          .Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents
'End With

 

[Steve_R]

To trap cancel

    Dim myRng As Range
    On Error Resume Next
    Set myRng = Application.InputBox("Select cell with mouse", Type:=8)
    On Error GoTo 0
    
    If myRng Is Nothing Then
        MsgBox "You clicked cancel"
        Exit Sub
    End If

    MsgBox myRng.Address

 

[cgeorge4]

The first code you provided worked PERFECTLY - except I still have one issue.

It asks the user to select a range before the code runs. That is better than what I had requested.
It works whether rows, or columns, or a range of cells is selected.

1) I didn't understand your green comment: 'remove when you are happy. Please clarify what I need to do with that

2) I get a Run-time error '1004' "No cells were found" if I select a cell or a range that has no hard-coded data (a blank range for example).


I looked at the second code you sent me but I don't know how to place it in the module. Could you help me with that as well? Thanks!!

FYI - This is what my code looks like right now:

Sub DELETE_Hardcode_Selected_Range()

Set myRng = Application.InputBox( _
prompt:="Select a cell or range or cells", Type:=8)
Debug.Print myRng.Address 'remove when you are happy


With myRng
.Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents
End With
End Sub

 

[Steve_R]

The second set of code allows a user to cancel gracefully.

The first code you provided worked PERFECTLY

I would have said, "less than perfect" if it returns an error!

1) I didn't understand your green comment: 'remove when you are happy.

Adding a single apostrophe changes a line from code interpretted by the compiler into comment ignored by the compiler. This allows you to write notes or 'comment out' sections of code while testing other code, etc.

The debug line was ther to demonstrate the address being collected by the user selection (i.e. so you could verify it works). All a debug line does is write to the Immediate window. Debug lines are not required for code to work. In your final version of code, its a good idea to delete debug lines or use a single apostrophe to comment them out.

2) I get a Run-time error '1004' "No cells were found" if I select a cell or a range that has no hard-coded data (a blank range for example).

Error handling is required. Error trapping now added to the code below. Trapping error is an important part of writing code. For more information, it is probably best to read the chapter in a VBA manual but the following website may help you understand the addition.
Error-Handling Techniques in Excel VBA

Sub Test()
    
Dim myRng As Range
    On Error Resume Next
    Set myRng = Application.InputBox("Select cell with mouse", Type:=8)
    On Error GoTo 0
    
    On Error GoTo MySub_Error
    
    If myRng Is Nothing Then
        'MsgBox "You clicked cancel"
        Exit Sub
    End If
    'Err.Raise (1004) 'I used this to induce error 1004 to test the error trap
    
    MsgBox myRng.Address
    
    '{your code}

'EXAMPLE
'With myRng
'          .Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents
'End With


MySubExit:

' If you switch off automatic calculation, screen updating or events, switch them back on between this line and Exit Sub

Exit Sub

' **********************error trap ******************************
' code that runs without error will exit before this point
MySub_Error:

Select Case Err

Case Is = 1004 'nothing found
    'do nothing or
    'MsgBox "Nothing requires change"
Case Else
    MsgBox "Error number " & Err & Err.Description & vbCrLf & "The above error number needs to be added to the error trap in {whatever you name the sub}"
End Select

GoTo MySubExit 'Loop back to pick up anything that needs to be cleaned up before exit.

End Sub