Clear content of several ranges (inc Message)

mattbarb

New Member
Joined
Mar 22, 2018
Messages
41
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,884
Office Version
2007
Platform
Windows
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
 

mattbarb

New Member
Joined
Mar 22, 2018
Messages
41
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.
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

nr6281

New Member
Joined
Jun 19, 2019
Messages
37
To ensure I understand correctly you are looking for (INC) to be removed of all cells?


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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,884
Office Version
2007
Platform
Windows
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.

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
 

Forum statistics

Threads
1,082,258
Messages
5,364,081
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top