Macro to delete specific columns in excel

[jesuscares]

Hello guys

Is there a way to delete specific columns particularly in ranges for example A: AO then AM: AZ?

 

[doncarp]

Here is code to delete column A. Just change the "A:A" to the columns you want to delete.

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

 

[jesuscares]

Thanks doncarp! However, I need to delete the two ranges listed above. Is there a way to do that?

 

[vcoolio]

Hello Jesuscares,

Here's another option. You can delete columns by range using an Input Box.

Sub DelCols()
   Dim Rng As Range
   Dim Cell As Range
   
   On Error Resume Next
   Set Rng = Application.InputBox(Prompt:="Select cells or ranges in the column(s) you wish to delete.", Title:="Select Cells or Ranges", Type:=8)
   If Not Rng Is Nothing Then
        For Each Cell In Rng
             Cell.EntireColumn.Delete
        Next
   End If
End Sub

You can peruse my test work book here.


https://www.dropbox.com/s/tbuj0xvp5a3l4i2/Delete Columns by Range Input Box(2).xlsm?dl=0

There are notes in the text work book to guide you.

I hope that this helps.

Cheerio,
vcoolio.

 

[jesuscares]

ADVERTISEMENT

Hello vcoolio

Ok so if I type in A:AO and AM:AZ in the prompt section, this will work? I am also confused with the Title part of the code.

 

[slinky]

Bear in mind that once you delete A:AO, AM is no longer AM.
I always find it's best to delete from right -> left ..

 

[jesuscares]

Hello guys. Thanks a lot for the input! I came across something a bit simpler and more tailored to my needs:

Sub DelCols()
Range("A:AK,AM:AZ").EntireColumn.Delete
End Sub

Sorry about the typo slinky! AO was a mistake on my part. However I am getting a code interrupted error with the End Sub statement being highlighted in yellow. Any ideas?

 

[jesuscares]

Hey slinky you are the man!! It worked!