Kathryn Tran
asked on
Clear Old Data in Excel with VBA Script
Hello Experts,
I am looking for a VBA code to clear dynamic range of a specific sheet named "Data".
Row 2 has header, from row 3 to last row and last column.
Thanks in Advance.
I am looking for a VBA code to clear dynamic range of a specific sheet named "Data".
Row 2 has header, from row 3 to last row and last column.
Thanks in Advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Much simpler would be to use Excel's inbuilt Range property - CurrentRegion. The CurrentRegion is a range bounded by any combination of blank rows and blank columns.
Option Explicit
Option Explicit
Sub ClearData()
Dim rData As Range
Set rData = Worksheets("Data").Range("A2").CurrentRegion
With rData
.Offset(1, 0).Resize(.Rows.Count - 1, _
.Columns.Count).ClearContents
End With
End Sub
ASKER
Thanks so much for your kind and quick help on this. Didn't expect a quick response from you. I tried your code and it works perfectly.
Much Appreciated !