ISSUE: Using VBA to Delete a Column
 

Hello all,

I'm using a VBA routine that creates a copy of a worksheet under a different
filename. The routine needs to delete column (D) before saving the new file,
however column D passes through a group of merged cells (A1 through D1).
When the command reaches this point:
Columns("D:D").Select
Range("D2").Activate,
the entire range of cells from A:D are selected. I could use the mouse to
manually select column D and delete it, but why wont VBA perform in the same
manner.

Here is the entire sub if it helps:

Sub Create_ECP_Copy()
'
'
Dim Promo As String, Rev As String, FileName As String

'Build File Name from Global Header Section
Promo = Range("D7").Text
Rev = " Rev " + Range("D8").Text
FileName = "Storage Promo " + Promo + Rev + " for ECP.xls"

'Copy 'On Promotion' Page to New File and Rename
Sheets("On Promotion").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D:D").Select
Range("D2").Activate
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\sada1.AMERICAS\My Documents\" + FileName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Windows("Master NSS Promotion Matrix.xls").Activate
Sheets("Global Set-Up Page").Select

End Sub

Any insight would be greatly appreciated.



--
Regards...

Merged cells are a pain.

What happens if you change this:

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

to just

Columns("D:D").Delete

(maybe avoiding the selection will avoid the problem--maybe not!)

If it doesn't help, you may want to post back with the version of excel that
you're using.

IIRC, xl97 would delete the same way as your code does if done manually. Later
versions of excel got nicer--but maybe not VBA????

cincode5 wrote:

Hello all,

I'm using a VBA routine that creates a copy of a worksheet under a different
filename. The routine needs to delete column (D) before saving the new file,
however column D passes through a group of merged cells (A1 through D1).
When the command reaches this point:
Columns("D:D").Select
Range("D2").Activate,
the entire range of cells from A:D are selected. I could use the mouse to
manually select column D and delete it, but why wont VBA perform in the same
manner.

Here is the entire sub if it helps:

Sub Create_ECP_Copy()
'
'
Dim Promo As String, Rev As String, FileName As String

'Build File Name from Global Header Section
Promo = Range("D7").Text
Rev = " Rev " + Range("D8").Text
FileName = "Storage Promo " + Promo + Rev + " for ECP.xls"

'Copy 'On Promotion' Page to New File and Rename
Sheets("On Promotion").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D:D").Select
Range("D2").Activate
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\sada1.AMERICAS\My Documents\" + FileName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Windows("Master NSS Promotion Matrix.xls").Activate
Sheets("Global Set-Up Page").Select

End Sub

Any insight would be greatly appreciated.

--
Regards...

--

Dave Peterson