Delete Entire Columns in Excel Unconditionally

OK, my VBA Excel skill may not be top dollar; however this should be easy, but the solution is eluding me.

All I am trying to do is delete columns AB thru AE using VBA; I can do it successfully the manual way ... open the spreadsheet, highlight the columns, right click, delete and everything shifts left as expected and column formatting is maintained.

Although I have to do this for all reps and would like to automate the process using VBA; this is where it does not work as expected and I have tried the following, but get the same results each time, see the attached screen shots ...

    Application.Goto Reference:="R1C28"
    ActiveWindow.SmallScroll ToRight:=4
    Columns("AB:AE").Select
    Selection.Delete Shift:=xlToLeft

    Selection.SpecialCells(xlCellTypeBlanks).Select
    Columns("AB:AE").Select
    Selection.Delete Shift:=xlToLeft
   
    Range("AB:AE").Select
    Selection.Delete

    Range("AB:AE").Select
    Selection.EntireColumn.Delete

    Range("AB1:AE1048576").Select
    Selection.Delete
   
    Range("AB1:AE1048576").Select
    Selection.EntireColumn.Delete

    Columns("AB:AE").Delete Shift:=xlToLeft

So what am I missing?
Excel-Before.jpg
Excel-After.jpg
Excel-Manual.jpg
Frank BryantJOATAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven CarnahanNetwork ManagerCommented:
Try this:

Sub Delete_Column_Excel_VBA()
   Columns("AB:AE").Select
   Selection.Delete Shift:=xlToLeft
End Sub

Open in new window

0
Steven HarrisPresidentCommented:
You don't need the Shift:=xlToLeft if you use Excel 2010, that is just an included function when you use the Macro Recorder.

All you will need is:

    Columns("AB:AE").Select
    Selection.Delete

Open in new window


However, pony10us brings a point to mind.

If in doubt, use the Record Macro option to get a general idea of what is going on in VBA language.
0
Steven CarnahanNetwork ManagerCommented:
I agree you don't need it if you are deleting the entire column as in this case.  

If you are only deleting some cells and want them to shift then you do need it at least in 2007 which I am still using.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Steven HarrisPresidentCommented:
You are correct pony10us.

Also, I noticed in your images you have specific column widths.  You may need to adjust those after you delete the columns.

For AutoFit use:
Columns("AB:AE").AutoFit

Open in new window


To specify the width in millimeters (I used 35 as an example):
Columns("AB:AE").ColumnWidth=35

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank BryantJOATAuthor Commented:
OK, I solved my own problem, Proof Read your code, I cannot express how important proof reading is. I had one line of code in the wrong place! Moved it to the right place and it works as advertised. Please see the attached VBA Module.
Excel-Auto-Processing-Code.txt
0
Frank BryantJOATAuthor Commented:
For what it is worth, all your coding suggestions worked, and at some point during those code trials is when I notice that the data was being imported after the column deletions.

Thanks again for all your suggestions!
0
Steven CarnahanNetwork ManagerCommented:
That would do it.  Glad you found it and it's working now.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.