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
我认为我的语法Columns(Variable_name)是错误的,但无法确定哪些方法有效
I tried Columns("Variable_name")
which didn't work too.
我尝试了Columns(“Variable_name”)也没有用。
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
3 个解决方案
#1
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
#2
2
You can use the Union
to merge all your columns to one Range
, and then delete it.
您可以使用Union将所有列合并到一个Range,然后将其删除。
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
#3
0
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.
我已经在案例表中包含了错误处理。也是完整的声明。我还在不同的行上放置了声明和作业,以便于阅读。
#1
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
#2
2
You can use the Union
to merge all your columns to one Range
, and then delete it.
您可以使用Union将所有列合并到一个Range,然后将其删除。
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
#3
0
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.
我已经在案例表中包含了错误处理。也是完整的声明。我还在不同的行上放置了声明和作业,以便于阅读。