Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cincode5
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort by row instead of by column? PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM
In a column of text data, how do I delete random cells that have . Minivann Excel Discussion (Misc queries) 2 January 26th 05 10:07 PM
I want to delete rows with duplicate entries within one column. kini olegario Excel Discussion (Misc queries) 1 January 15th 05 02:44 AM
Copying the contents of a column into a chart Richard Excel Worksheet Functions 1 November 16th 04 03:39 PM
How to calculate the data in excel 2002 including only the last 9. TylerMaricich Excel Worksheet Functions 6 November 8th 04 08:27 AM


All times are GMT +1. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"