Monday, May 10, 2010

How do I clear rows in Excel spreadsheet with a macro?

Do you have a huge spreadsheet with a ton of rows that you need to delete each time you use it? Here is a simple macro to help you. This is for a spreadsheet that is password protected. Now, I have headers in row 1 and formulas starting in row 2 that I don't want to wipe away so I select the range starting in row 3.

Sub Clear_Rows()

''' Protect User Interface Only '''

ActiveWorkbook.Sheets("sheet1").Activate

Sheets("sheet1").Protect Password:="pass1", UserInterfaceOnly:=True


 

''' Clear Rows '''

Range("a3:xfd1048576").Select

Selection.ClearContents

Range("a1").Select

End Sub