Columns("G:G").Select - Selects Entire Page?...

[SGTBARRETT]

I have written the following code to remove unwanted items from a sheet that is produced daily, elsewhere in my company. But for some reason, The first command selects the entire sheet and of course the delete command then deletes everything. I have a few columns that are at zero width, but I have even added script to set those to a width of 5 and it affects nothing. Everything still gets deleted.

If I run this on an empty sheet, it seems to work fine. Just not on the generated sheet. I'm gonna pull my hair out now...

-----------------------
Sub FormatCells()
'
' FormatCells Macro
'

'

Columns("G:G").Select
Range("G17").Activate
Selection.Delete Shift:=xlToLeft

Rows("1:9").Select
Range("A9").Activate
Selection.Delete Shift:=xlUp

Columns("M:Q").Select
Selection.Delete Shift:=xlToLeft

Columns("D:E").Select
Range("D17").Activate
Selection.Delete Shift:=xlToLeft

Columns("C:C").Select
Selection.ColumnWidth = 24.86

Columns("H:H").ColumnWidth = 55.86
Rows("2:60").Select
Selection.RowHeight = 24.75

End Sub

 

[lrobbo314]

Does skipping the selection process fix the problem?

Sub FormatCells()
Columns("G:G").Delete shift:=xlToLeft
Rows("1:9").Delete shift:=xlToRight
Columns("M:Q").Delete shift:=xlToLeft
Columns("D:E").Delete shift:=xlToLeft
colmns("C:C").Delete shift:=xlToLeft
Columns("H:H").ColumnWidth = 55.86
Rows("2:60").rowheight = 24.75
End Sub

 

[steve the fish]

Do you have merged cells? See if this behaves any differently:

Columns("G:G").Delete Shift:=xlToLeft

 

[SpillerBD]

Use the Debug\Step Into to watch each step this recorded macro performs.
Its doing exactly what it says and if run more than once it will delete everything on the worksheet.
The first step could be simplified to:
Columns("G:G").Delete Shift:=xlToLeft 'Deletes Column "G"

You also Delete Rows 1 through 9 with
Rows("1:9").Select
Range("A9").Activate
Selection.Delete Shift:=xlUp

This could be simplified to
Rows("1:9").Delete Shift:=xlUp ' Delete Row 1 thru 9

But deleting Row 1? Usually that is were headers are so that seems dubious to me.
And finally you delete columns D & E...?

Maybe you should consider Clear instead of Delete?

 

[SGTBARRETT]

IM AN IDIOT...

I failed to realize that the last line of the sheet was all the Columns merged together to form a footer... Never mind...

I cleaned up the code to look like this. Note the unmerge command at the top... LOL!!

Sub FormatCells()
'
' FormatCells Macro
'
Range("A1:O60").UnMerge

Rows("1:9").Delete Shift:=xlUp

Columns("A:A").ColumnWidth = 7.86

Columns("L:Q").Delete Shift:=xlToLeft

Columns("G:G").Delete Shift:=xlToLeft

Columns("D:E").Delete Shift:=xlToLeft

Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

Columns("B:B").ColumnWidth = 28.86
Columns("G:G").ColumnWidth = 55.86

Rows("2:60").RowHeight = 24.75

End Sub

 

[SpillerBD]

Do you have merged cells? See if this behaves any differently:

Columns("G:G").Delete Shift:=xlToLeft

Yo! Life is better WITHOUT Merged cells