Clear contents in table but leave columns with formulas

[thedeadzeds]

Hi Guys,

I have 2 tables one named 'Steve' and the Other named 'Dave'.

Is there a way to use VBA to clear the contents of both tables but leave the formulas in columns R,S,T,U,V. At the moment i just select all rows from row 3 down (leaving the heading and row 2) and delete . I then paste the new data into A2 as values and the formulas in R,S,T,U,V auto copy down.

Thanks


[TABLE="width: 2614"]
<colgroup><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hey, how about something like this:

Sub ClearNonFormulaeSteveAndDave()
    Dim cell As Range
    Dim rngSteve As Range
    Dim rngDave As Range
    Set rngSteve = Range("Steve")
    Set rngDave = Range("Dave")
        For Each cell In rngSteve
            If cell.HasFormula = False Then
                cell.ClearContents
            End If
        Next cell
        For Each cell In rngDave
            If cell.HasFormula = False Then
                cell.ClearContents
            End If
        Next cell
End Sub

I'd save your work before testing it but it should delete all non formula cells from the tables "Steve" and "Dave".

 

[James006]

Hello to Steve and Dave ...