Macro To Clear Cell Contents From Top To Bottom Of A Column

**kany** · 01-01-2013, 01:09 AM

I would greatly appreciate if anyone could help me with the following.

I have a macro that would clear numerical contents of each cell in column A every one minute (initiated by the click of a command button linked to this macro).

However, this macro begins to clear contents from the last cell in column (i.e A65536) and moves upwards.

(a) What I actually need is for the reverse to happen, starting to clear cells beginning A1(or a specified cell) and move downwards every minute till A65536.

(b) Also, I would want the same to happen to cells in columns B till AB.

Thank you very much in advance.

My code is as follows

Option Explicit
Public dTime As Date

Sub ClearCell()
Dim c As Range
For Each c In Range("A65536").End(xlUp)
If IsNumeric(Val(c)) Then c.ClearContents
Next c
Call StartTimer
End Sub

Sub StartTimer()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "ClearCell", Schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime dTime, "ClearCell", Schedule:=False
End Sub

**AlphaFrog** · 01-01-2013, 01:42 AM

Try something like this. Change the sheet name to suit.

Public dTime   As Date
    
Sub ClearCell()
    Static rng As Range
    With Sheets("Sheet1")
        If rng Is Nothing Then Set rng = .Range("A1:AB1")   'Start cells
        rng.ClearContents
        If rng.Row < .Rows.Count Then
            Set rng = rng.Offset(1)
            Call StartTimer
        End If
    End With
End Sub
    
Sub StartTimer()
    dTime = Now + TimeValue("00:01:00")
    Application.OnTime dTime, "ClearCell", Schedule:=True
End Sub
    
Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ClearCell", Schedule:=False
End Sub

**chriswiec** · 01-01-2013, 01:46 AM

This might work
Range("A1:AB65536").FormulaR1C1 = clearcontents
Try Adding that to you or the code above with your timer this works from top to bottom.

**kany** · 01-01-2013, 02:16 AM

I wish to extend my sincere thanks to both AlphaFrog and Chriswiec for the prompt and kind assistance given to me. I tried and the two methods worked great, just as what I wanted. If not for you guys and Excel Forum, i would be scratching my head for next countless days as what I've doing for the past few days.

Again, thank you so much for making me learn something new about macro programming and completing my project. And, not forgetting, Happy New Year 2013 to both of you as well as the Excel Forum family.

Warm regards.