Clear content of several ranges (inc Message)

M

mattbarb

Board Regular
Joined
Mar 22, 2018
Messages
54
Office Version
  1. 365
Platform
  1. MacOS
Hi,

For the life of me this doesnt work, any ideas?

Many thanks

Code: 
Sub Clear_Estimated_Hours()    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?"    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Delete Estimated Hours"    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then    ' User chose Yes.
    thissheet.Range ("V20:V41")
.Range ("AB20:AB41")
.Range ("AH20:AH41")
.Range ("AN20:AN41")
.Range ("AT20:AT41")
.Range ("AZ20:AZ41")
.Range ("BF20:BF41")
.Range ("BL20:BL41")
.Range ("BR20:BR41")
.Range ("BX20:BX41")
.Range ("CD20:CD41")
.Range ("CJ20:CJ41")
.Range ("CP20:CP41")
.Range ("CV20:CV41")
.Range ("DB20:DB41")
.Range ("DH20:DH41")
.Range ("DN20:DN41")
.Range ("DT20:DT41")
.Range ("DZ20:DZ41")
Else    ' User chose No.
    MyString = "No"    ' Perform some action.
End If
   
   


End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Try this:

Code: 
Sub Clear_Estimated_Hours()
  Dim Response As Variant, MyString As String
  Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + vbDefaultButton2, _
    "Delete Estimated Hours", "DEMO.HLP", 1000)
  If Response = vbYes Then    ' User chose Yes.
    With ActiveSheet
      .Range("V20:V41").ClearContents
      .Range("AB20:AB41").ClearContents
      .Range("AH20:AH41").ClearContents
      .Range("AN20:AN41").ClearContents
      .Range("AT20:AT41").ClearContents
      .Range("AZ20:AZ41").ClearContents
      .Range("BF20:BF41").ClearContents
      .Range("BL20:BL41").ClearContents
      .Range("BR20:BR41").ClearContents
      .Range("BX20:BX41").ClearContents
      .Range("CD20:CD41").ClearContents
      .Range("CJ20:CJ41").ClearContents
      .Range("CP20:CP41").ClearContents
      .Range("CV20:CV41").ClearContents
      .Range("DB20:DB41").ClearContents
      .Range("DH20:DH41").ClearContents
      .Range("DN20:DN41").ClearContents
      .Range("DT20:DT41").ClearContents
      .Range("DZ20:DZ41").ClearContents
    End With
  Else    ' User chose No.
    MyString = "No"    ' Perform some action.
  End If
End Sub
 
Upvote 0
Thanks!!!

Quick question if i wanted to show an error if the cells are protected how would i do it. Currently these cells are locked so i get a VBA error.
 
Upvote 0
Hi
For Post#1
What about
Code: 
Sub Clear_Estimated_Hours()
  Dim Response As Variant, MyString As String
  Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + vbDefaultButton2, _
    "Delete Estimated Hours", "DEMO.HLP", 1000)
  If Response = vbYes Then    ' User chose Yes.
    With ActiveSheet
     Set Rng = Union(.Range("V20:V41"), .Range("AH20:AH41"), _
     .Range("AN20:AN41"), .Range("AT20:AT41"), .Range("AZ20:AZ41"), _
     .Range("BF20:BF41"), .Range("BL20:BL41"), .Range("BR20:BR41"), _
     .Range("BX20:BX41"), .Range("CD20:CD41"), .Range("CJ20:CJ41"), _
     .Range("CP20:CP41"), .Range("CV20:CV41"), .Range("DB20:DB41"), _
     .Range("DH20:DH41"), .Range("DN20:DN41"), .Range("DT20:DT41"), _
     .Range("DZ20:DZ41"))
     Rng.ClearContents
    End With
  Else    ' User chose No.
    MyString = "No"    ' Perform some action.
  End If
End Sub
 
Upvote 0
ANd
Code: 
Sub Clear_Estimated_Hours()
    Dim Response As Variant, MyString As String
    Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + vbDefaultButton2, _
                      "Delete Estimated Hours", "DEMO.HLP", 1000)
    If Response = vbYes Then    ' User chose Yes.
        With ActiveSheet
            Set Rng = Union(.Range("V20:V41"), .Range("AH20:AH41"), _
                            .Range("AN20:AN41"), .Range("AT20:AT41"), .Range("AZ20:AZ41"), _
                            .Range("BF20:BF41"), .Range("BL20:BL41"), .Range("BR20:BR41"), _
                            .Range("BX20:BX41"), .Range("CD20:CD41"), .Range("CJ20:CJ41"), _
                            .Range("CP20:CP41"), .Range("CV20:CV41"), .Range("DB20:DB41"), _
                            .Range("DH20:DH41"), .Range("DN20:DN41"), .Range("DT20:DT41"), _
                            .Range("DZ20:DZ41"))
[COLOR=#0000ff]            On Error GoTo 1[/COLOR]
            Rng.ClearContents
        End With
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
    End If
[COLOR=#0000ff]1   MsgBox "Cells Protected"[/COLOR]
End Sub
 
Upvote 0
To ensure I understand correctly you are looking for (INC) to be removed of all cells?


mattbarb said:
Hi,

For the life of me this doesnt work, any ideas?

Many thanks

Code: 
Sub Clear_Estimated_Hours()    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?"    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Delete Estimated Hours"    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then    ' User chose Yes.
    thissheet.Range ("V20:V41")
.Range ("AB20:AB41")
.Range ("AH20:AH41")
.Range ("AN20:AN41")
.Range ("AT20:AT41")
.Range ("AZ20:AZ41")
.Range ("BF20:BF41")
.Range ("BL20:BL41")
.Range ("BR20:BR41")
.Range ("BX20:BX41")
.Range ("CD20:CD41")
.Range ("CJ20:CJ41")
.Range ("CP20:CP41")
.Range ("CV20:CV41")
.Range ("DB20:DB41")
.Range ("DH20:DH41")
.Range ("DN20:DN41")
.Range ("DT20:DT41")
.Range ("DZ20:DZ41")
Else    ' User chose No.
    MyString = "No"    ' Perform some action.
End If
   
   


End Sub
Click to expand...
 
Upvote 0
mattbarb said:
Thanks!!!

Quick question if i wanted to show an error if the cells are protected how would i do it. Currently these cells are locked so i get a VBA error.
Click to expand...


Try this:

Code: 
Sub Clear_Estimated_Hours()
  Dim Response As Variant, MyString As String
  Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + vbDefaultButton2, _
    "Delete Estimated Hours", "DEMO.HLP", 1000)
  If Response = vbYes Then    ' User chose Yes.
    With ActiveSheet
[COLOR=#0000ff]      if .ProtectContents Then
[/COLOR][COLOR=#0000ff]        MsgBox "The cells are protected", vbExclamation[/COLOR]
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]      end if[/COLOR]
         [COLOR=#333333]
[/COLOR]
      .Range("V20:V41").ClearContents
      .Range("AB20:AB41").ClearContents
      .Range("AH20:AH41").ClearContents
      .Range("AN20:AN41").ClearContents
      .Range("AT20:AT41").ClearContents
      .Range("AZ20:AZ41").ClearContents
      .Range("BF20:BF41").ClearContents
      .Range("BL20:BL41").ClearContents
      .Range("BR20:BR41").ClearContents
      .Range("BX20:BX41").ClearContents
      .Range("CD20:CD41").ClearContents
      .Range("CJ20:CJ41").ClearContents
      .Range("CP20:CP41").ClearContents
      .Range("CV20:CV41").ClearContents
      .Range("DB20:DB41").ClearContents
      .Range("DH20:DH41").ClearContents
      .Range("DN20:DN41").ClearContents
      .Range("DT20:DT41").ClearContents
      .Range("DZ20:DZ41").ClearContents
    End With
  Else    ' User chose No.
    MyString = "No"    ' Perform some action.
  End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Finding date in range of cells
2
Replies
15
Views
758
westwegoman
W
CDICKENS
  • Question
subscript out of range error loading userform
Replies
2
Views
13K
CDICKENS
CDICKENS
J
  • Question
msgbox and range name
Replies
2
Views
6K
jaypatel
J

Forum statistics

Threads
1,214,641
Messages
6,120,692
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top