Delete Columns from Excel using VBA

Hi,

I need to delete 2 adjacent columns which is placed somewhere in the middle of a table. So how can i select 2 adjacent columns and deletes the selected entire columns using vba?

Top Contributor Asked on December 13, 2017 in VBA: Macros.
Add Comment
3 Answer(s)

This will delete columns 3 and 4 of Table 1 on Sheet 1.

Sub delcol3_4()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(“Table1”)

For i = 1 To 2
With Worksheets(“Sheet1”)
tbl.ListColumns(3).Delete
End With
Next i

End Sub

Expert Answered on December 30, 2017.
Add Comment

Hi,

Thanks for the reply. I need to find a formula for finding the column number, i can’t give a hard coded number.

Top Contributor Answered on January 1, 2018.

What is the criteria for looking up the column number.  Do your columns have headers?

on January 1, 2018.

Added input box to choose col to delete. This will delete that col and the adjacent col to the right.  If you have problems with the macro replace the quotation marks with new quotation marks and try again.

New VBA:

Sub del2adjacentcols()
Dim tbl As ListObject
Dim i As Integer
Dim Colnbr As Integer
On Error GoTo Line1
Set tbl = ActiveSheet.ListObjects(“Table1”)
Colnbr = InputBox(“What Column would you like to delete from table 1 ?”)

For i = 1 To 2
With Worksheets(“Sheet1”)
tbl.ListColumns(Colnbr).Delete
End With
Next i

Line1:

End Sub

RE: Delete Columns from Excel using VBA

on January 1, 2018.
Add Comment

Hello

Can you upload sample of your workbook?

Expert Answered on January 1, 2018.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.