How to clear contents of an Excel Workbook through vba

0 votes

I'm using a master worksheet to automate data. I want a message box to display and clear the contents of particular columns in tables in one of the worksheets when I start the workbook. The "Application-defined or object-defined error" keeps occurring. The code for my "This workbook" section is as follows:

Option Explicit

Sub Workbook_Open()

Dim answer As Integer

answer = MsgBox("Do you want to clear the totals?", vbYesNo + vbQuestion, "Clear Totals")

If answer = vbYes Then

Call Sheet1.ClearContents_1

End If

End Sub

This is my Sheet1 code:

Sub ClearContents_1()

Call Loop_Clear_C

Call Loop_Clear_M

Call Clear_S

End Sub

Sub Loop_Clear_C()

For i = 1 To Range("UserTable79").Rows.Count
    Range("UserTable79[Total]")(i) = 0
Next i
End Sub

Sub Loop_Clear_M()

For i = 1 To Range("ServiceTable79").Rows.Count
    Range("ServiceTable79[Total]")(i) = 0
Next i
End Sub

Sub Clear_S()

Range("TotalTable79[Actual Total]").ClearContents

End Sub

They worked separately but not together. The msg box comes up but doesn't run the Sheet1 code. How do I call upon this sheet code?

Nov 19 in Others by Kithuzzz
• 20,660 points
17 views

1 answer to this question.

0 votes
When the range you're referring to doesn't exist, the "Application-defined or object-defined error" notice frequently appears. When you activate Sheet1 by clicking on it, try using the Immediate panel to execute an operation on the ranges you're referring to (try entering Range in the Immediate panel, for example) ("UserTable79"). Get an error when you select)?

When calling a function from the "ThisWorkbook" section, the worksheet must be specified explicitly (for example, if the worksheet is named "SheetName," you must specify Call ThisWorkbook).

Sheets("SheetName").

It may be easier to use ClearContents 1 rather than Call Sheet1.ClearContents 1).
answered Nov 19 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
247 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

How to calculate percentage of an autosum field in excel

The fact that I was using the ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
26 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
17 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
449 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,199 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
66 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
199 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
110 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
29 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP