Excel VBA: Delete multiple columns using variable containing column numbers

Question

Because of the high chances of the arrangement of columns being adjusted in my raw data, I want to store the column numbers in variables.

I think that my syntax Columns(Variable_name) is wrong, but can't figure what will work

I tried Columns("Variable_name") which didn't work too.

Set Cws = Worksheets.Add
Cws.Name = "Ready_For_Send"

Dim Region As Integer: Region = 1
Dim Sub_Region As Integer: Sub_Region = 2
Dim User_Status As Integer: User_Status = 5
Dim Count As Integer: Count = 15

With Cws
  .Range(.Columns(Region) & "," & .Columns(Sub_Region) & "," & .Columns(User_Status) & "," & Columns(Count)).Delete
End With

What kind of error message are you getting? What line is the code failing at? Part of the code appears to be missing e.g. there needs to be a reference to the workbook you are adding the sheet too and also a declaration of the worksheet Cws as worksheet. — QHarr, Oct 03 '17 at 08:05

Fabien Bouleau · Answer 1 · 2017-10-03T08:23:44.103

3

You can use the following:

With Cws
    .Range(Cells(1, Region).EntireColumn.Address & "," _
      & Cells(1, Sub_Region).EntireColumn.Address & "," _
      & Cells(1, User_Status).EntireColumn.Address & "," _
      & Cells(1, Count).EntireColumn.Address).Delete
End With

edited Oct 03 '17 at 08:23

answered Oct 03 '17 at 08:17

Fabien Bouleau

464
3
11

score 2 · Answer 2 · answered Oct 03 '17 at 08:09

You can use the Union to merge all your columns to one Range, and then delete it.

Try the code below:

Dim DelRng As Range

With Cws
    ' Set a new range from all the columns you want to delete
    Set DelRng = Union(.Columns(Region), .Columns(Sub_Region), .Columns(User_Status), .Columns(Count))
    DelRng.Delete
End With

score 1 · Answer 3 · edited Oct 03 '17 at 08:45

May be something like this:

Option Explicit

Sub DeleteCols()

Dim wb As Workbook
Dim Csw As Worksheet
Dim Region As Long
Dim Sub_Region As Long
Dim User_Status As Long
Dim Count As Long
Dim Cws As Worksheet

Region = 1
Sub_Region = 2
User_Status = 5
Count = 15

Set wb = ThisWorkbook

Application.DisplayAlerts = False
On Error Resume Next
Set Cws = wb.Worksheets.Add
Cws.Name = "Ready_For_Send"
On Error GoTo 0
Application.DisplayAlerts = True

With Cws

.Range( _
           ReturnName(Region) & ":" & ReturnName(Region) & "," & _
           ReturnName(Sub_Region) & ":" & ReturnName(Sub_Region) & "," & _
           ReturnName(User_Status) & ":" & ReturnName(User_Status) & "," & _
           ReturnName(Count) & ":" & ReturnName(Count) _
           ).Delete Shift:=xlToLeft
End With

End Sub

Function ReturnName(ByVal num As Integer) As String
   ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

Some structure and Function from here: Delete multiple columns

I have included error handling in case sheet already exists. Also full declarations. I have also put declarations and assignments on different lines for ease of reading.

Excel VBA: Delete multiple columns using variable containing column numbers

3 Answers3