PDA

View Full Version : [SOLVED] Clear all cells vba



PhancyK
08-19-2016, 09:10 AM
Is there a way to create a macro to clear all cells below a table? The table size is always changing so I can't just set a row and leave it.

Any ideas?

Leith Ross
08-19-2016, 09:50 AM
Hello PhancyK,

Do you want to clear all the cells in the table except the headers?

PhancyK
08-19-2016, 10:01 AM
Was able to figure it out I used the following:


Sub ClearUnderTable()
Dim tr As Long, trc As Long, tc As Long, tcc As Long

tr = Range("Table1").Row
trc = Range("Table1").Rows.Count
tc = Range("Table1").Column
tcc = Range("Table1").Columns.Count

Range(Cells(tr + trc, tc), Cells(1000, tc + tcc - 1)).ClearContents

End sub

SamT
08-19-2016, 10:18 AM
Sub SamT()
Dim Col1 As Long
Dim Col2 As Long
Dim Row1 As Long

With Range("Table1")
Col1 = .Cells(1).Column
Col2 =.Cells(.Cells.Count).Column
Row1 = .Cells(.Cells.Count).Row + 1
End With

Range(Cells(Row1, Col1), Cells(Rows.Count, Col2)).Delete Shift:=xlShiftUp 'Keeps UsedRange smallest
End Sub

mancubus
08-19-2016, 01:04 PM
?

Range("Table1).Offset(1).Clear)