PDA

View Full Version : Reset MOST of Worksheet with Macro



karrotman
07-26-2012, 11:10 AM
Hi All,

I would like to add a button to a worksheet that will reset all cells to original state (empty, no highlighting, no bordering) with the exception of a few cells which I know in advance. Is there a relatively simple macro to do this?

karrotman
07-26-2012, 11:55 AM
I sort of cheated. I did this:

Sub ClearSheet1()

Dim rng

Set rng = Sheet1.Range("a3:b20,e3:o100")
rng.ClearContents
rng.Interior.ColorIndex = 0
Sheet1.Range("f3:g100").Borders.LineStyle = xlNone
'NEED TO FIGURE OUT HOW TO WRITE THIS AS ("F3:G&"INTERPLENGTH")

End Sub

The obvious problem is how do I know what length G100 is really going to be. See comment in code...

Tinbendr
07-26-2012, 02:17 PM
With Sheet1
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Range("f3:g" & LastRow).Borders.LineStyle = xlNone
End With