Deleting values on various sheets

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I have a command button of which when run it will delete certain cell value on different worksheets.
The worksheets are all in the same workbook called ACCOUNTS

The below code is what ive done so far BUT im stuck with the code that fits where you see CODE GOES HERE
The part which i always get wrong where to tell the code to plly the deletion to what sheet



Rich (BB code):
Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & vbNewLine & "ON ALL WORKSHEETS" & vbNewLine & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
  Exit Sub
Else
End If

CODE TO GO HERE

End Sub

REFERENCE BELOW FOR WHICH SHEETS / CELLS NEED TO BE DELETED

INCOME (1}

Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents

INCOME (2)

Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents

INCOME (3)

Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (1)

Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A4:K28").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (2)

Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (8)
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents

mileage (1)

Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Like so,
RTE( subscript out of range ??

Rich (BB code):
Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & vbNewLine & "ON ALL WORKSHEETS" & vbNewLine & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
Exit Sub

Else
End If
With ThisWorkbook.Sheets("INCOME(1)")
Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("INCOME(2)")
Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("INCOME(3)")
Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(1)")
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A4:K28").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(2)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(3)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(4)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(5)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(6)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(7)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("EXPENSES(8)")
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
End With

With ThisWorkbook.Sheets("MILEAGE")
Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
End With

End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
The above issue has now been fixed, space between word & (1) etc
Now when i run the code i see the RTE 1004 No cells were found.
I debug and see the following line highlied.

Im now not sure how to fix this as screenshot shows values are there ??

Code at present shown below


Rich (BB code):
Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & vbNewLine & "ON ALL WORKSHEETS" & vbNewLine & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
Exit Sub

Else
End If
With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("INCOME (2)")
Application.EnableEvents = False
Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("INCOME (3)")
Application.EnableEvents = False
Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (1)")
Application.EnableEvents = False
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A4:K28").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (2)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (3)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (4)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (5)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (6)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (7)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES( 8)")
Application.EnableEvents = False
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("MILEAGE")
Application.EnableEvents = False
Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With

End Sub
 

Attachments

  • 189.jpg
    189.jpg
    21.4 KB · Views: 1
  • 190.jpg
    190.jpg
    102.2 KB · Views: 2

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,119
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do the cells definitely contain constants and not formulas? asking because column B definitely looks like formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

At the moment your with statements are doing nothing & those ranges are all looking at the active sheet.
You need to put a full stop before the range like
VBA Code:
With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
.Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Do the cells definitely contain constants and not formulas? asking because column B definitely looks like formulas.

Column E are IF statements.
Row 30 & 32 are =SUM formulas
Other than that just numbers / letters

@Fluff I have now added the full stop as advised & things now are moving along with respect of deleting values.
There is one thing id like to ask.

Say INCOME (2) doesnt have any values, The code still tries to delete something that isnt there & thus pops up a RTE1004
How can i supress this error & just continue with the code.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
On Error Resume Next
.Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Hmmm,
That still tells me that no cells were found
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,253
Office Version
  1. 365
Platform
  1. Windows
Did you add those two lines to each of your with statements?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,184
Members
418,288
Latest member
reba557

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
Top