Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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... |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
In a column of text data, how do I delete random cells that have . | Excel Discussion (Misc queries) | |||
I want to delete rows with duplicate entries within one column. | Excel Discussion (Misc queries) | |||
Copying the contents of a column into a chart | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |