Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to code to clear Contents for number of worksheet

0

Hi Experts,

I am trying to write code to clear the contents when workbook Open, but finding error on the same.

Hence, Request for your help for the same.

Below code and attached sheet for your ready reference.

Option Explicit

Private Sub Workbook_Open()
    ' 206

    Dim Rl          As Long                 ' last used row in column A
    'Dim R2          As Long                 ' last used row in column A
    Dim DelPrev     As VbMsgBoxResult       ' User input
    
    DelPrev = MsgBox("Delete previous day's Min1 & Max1 & Max2 & Min3?" & vbCr & _
                     "Press Cancel to not start the Timer.", _
                     vbQuestion Or vbYesNoCancel Or vbDefaultButton1, _
                     "Start new day?")
    If DelPrev <> vbCancel Then
        If DelPrev = vbYes Then
            With Worksheets("Sheet1")
                Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
                .Range(.Cells(NwsFirstRow, NwsMax1), .Cells(Rl, NwsMax1)).ClearContents
                .Range(.Cells(NwsFirstRow, NwsMin1), .Cells(Rl, NwsMin1)).ClearContents
                '.Range(.Cells(NwsFirstRow, NwsMax2), .Cells(Rl, NwsMax2)).ClearContents
                '.Range(.Cells(NwsFirstRow, NwsMin3), .Cells(Rl, NwsMin3)).ClearContents
    
    With Worksheets("Sheet2")
                Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
                .Range(.Cells(Nws1FirstRow, Nws1Max1), .Cells(Rl, Nws1Max1)).ClearContents
                .Range(.Cells(Nws1FirstRow, Nws1Min1), .Cells(Rl, Nws1Min1)).ClearContents
                '.Range(.Cells(NwsFirstRow, NwsMax2), .Cells(Rl, NwsMax2)).ClearContents
                '.Range(.Cells(NwsFirstRow, NwsMin3), .Cells(Rl, NwsMin3)).ClearContents
                       
         End With
         End If
         SetTimer
         Set_Timer
                
         End If
         
End Sub

Would really appreciate your response.

Regards,
Answer
Discuss

Discussion

Sunil

I think you're turning to us to fix problems a little too quickly for small errors like this. I realise that you're developing fresh code but if you indent it properly from the start (as I think Variatus mentioned before), you will such detect errors more easily
John_Ru (rep: 6092) Aug 30, '21 at 7:16 am
Add to Discussion

Answers

0
Selected Answer

Sunil

There are at least two problems with your workbook at present. Firstly, you get the "without Block If" error on opening the file because you did not close the first With statement - add the bit in bold before the line below...:

        End With

        With Worksheets("Sheet2")....
Secondly you need to update the file name reference in your sub MyMacro (starting With Workbooks("Macro...) or change it to ThisWorkbook.Sheet...

Not sure why you check for both (not) vbCancel and VbYes (when you only act on vbYes so far) but I leave that to you.

Hope this helps.

Discuss

Discussion

Thank you John for the solution.
Very much Appreciated.
SunilA (rep: 56) Aug 30, '21 at 9:06 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login