Clearing DataTable But Retain Formulas

03856me

Active Member
Joined
Apr 4, 2008
Messages
261
I have a dataTable in Excel 2007 that contains columns A-P that are entered via a UserForm which works perfectly. There are formulas in columns Q through KO (285 columns, wow). So far so good.

Steps my user goes through:
Step 1) Enter the data via UserForm starting on row 2 of the dataTable
Step 2) Generate Reports, Graphs, Etc. for this project
Step 3) Saves the data only (columns A-P) to an external file (which can be imported later if need be)
Step 4) Clears dataTable to start new project

Steps 1 through 3 are working fine, it is step 4 that is causing the problem. When I clear the data in the table I leave row 1 intact so it keeps the formulas in columns Q-KO. This works if I do it manually but when I do it with this macro, the next time they add data the formulas don't continue down. I am beginning in the 2nd row of the dataTable, could that be the problem? Also, I am using EntireRow.Delete for rows 2 to the end because there could be 500 or so rows of data.

Your help is greatly appreciated.

Code:
Sub ResetData()
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
 
    Sheets("Main Menu").Select
    Sheets("data").Visible = True
    Sheets("DATA").Select
    ActiveSheet.Cells.EntireColumn.Hidden = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    Range("A7").Select
 
    Range("dataTable[[plot]:[vc3]]").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete  'ClearContents
 
'I tried this but then the formulas are gone completely 
'    Range("dataTable").Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
 
    Range("dataTable[[L1length]:[Form Class]]").Select
    Selection.EntireColumn.Hidden = True
    Range("A6").Select
    Sheets("data").Visible = False
 
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,250
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top