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
– QHarr, Commented Oct 3, 2017 at 8:05

Fabien Bouleau · Accepted Answer · 2017-10-03 08:23:44Z

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 3, 2017 at 8:23

answered Oct 3, 2017 at 8:17

Fabien Bouleau

4643 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-10-03 08:09:46Z

2

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

answered Oct 3, 2017 at 8:09

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Comments

Mrig · Accepted Answer · 2017-10-03 08:45:29Z

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.

Collectives™ on Stack Overflow

Excel VBA: Delete multiple columns using variable containing column numbers

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related