CODEWITHSUNDEEP
excelvba
Earl Mascetti
Earl Mascetti

Reputation: 1336

How to clean a column in a specific time in VBA

My focus is to clean a specific column in a specific sheet every one hours.

I wrote this code but doesn't work.

Sub CleanColumn()
   Worksheets("Calculation").Columns(2).ClearContents
   Call test
End Sub
Sub test()
    Application.OnTime Now + TimeValue("01:00:00"), "CleanColumn"
End Sub
Where I wronge?

Thanks in advance.

Upvotes: 0

Views: 77

Answers (2)

mooseman
mooseman

Reputation: 2017

cI got it to work by copying your code to Module1 in a workbook and changing to this: 

Sub test()
    Application.OnTime Now + TimeValue("01:00:o0"), "Module1.CleanColumn"
End Sub

I believe that you need to tell it where the subroutine CleanColumn resides Since it is on the hour, you should explicitly define which workbook and which worksheet like:

Workbook("\\f2\folder\wb_time.xlsm").Worksheets("Sheet1").Columns(2).ClearContents

Upvotes: 1

M. Antoney
M. Antoney

Reputation: 88

below code is working.

Public Const ClCol = "CleanColumn"


Sub CleanColumn()
  'Change the range as per your req..
   Sheet1.Range("A1").ClearContents
   Call test
End Sub


Sub test()
'Once you change the below time it will be adjusted accordingly.
    Application.OnTime Now + TimeValue("00:01:00"), ClCol

End Sub

Hope this helps..

Upvotes: 2

Related Questions