remove all format(s) on a sheet

[Vsn]

Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet, including
lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic

 

[JLGWhiz]

ActiveSheet.Cells.ClearFormats

 

[Lars-Åke Aspelin]

Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet, including
lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic

Try this:

1) Insert a new worksheet into the workbook.
2) Select everything on the new worksheet (CTRL+A)
3) Copy everything from the new worksheet (CTRL+C)
4) Select everything on the worksheet you want to clean up (CTRL+A).
5) Paste Special and select Formats in the Paste section.

This will reset the formatting to the same as for a new worksheet.
You can now delete the worksheet from step 1.

Hope this helps / Lars-Åke

 

[Rick Rothstein]

Assuming you want to set your fonts back to the default values as well, give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

 

[Lars-Åke Aspelin]

Assuming you want to set your fonts back to the default values as well, give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

Column Widths and Row Heights don't seem to be reset by .ClearFormats

Lars-Åke

 

[Rick Rothstein]

I wasn't sure if the OP wanted Column Widths and Row Heights reset as well,
but I doing that that makes sense. Assuming the last row and last column
have not been changed, then I guess you could "steal" the ColumnWidths and
RowHeights values from them...

Rows.RowHeight = Rows(Rows.Count).RowHeight
Columns.ColumnWidth = Columns(Columns.Count).ColumnWidth

 

[Vsn]

This works perfect, thanks a lot!

Ludovic

I wasn't sure if the OP wanted Column Widths and Row Heights reset as well,
but I doing that that makes sense. Assuming the last row and last column
have not been changed, then I guess you could "steal" the ColumnWidths and
RowHeights values from them...

Rows.RowHeight = Rows(Rows.Count).RowHeight
Columns.ColumnWidth = Columns(Columns.Count).ColumnWidth