使用数组删除用户输入列范围

Question

我正在学习使用数组。

下面的代码是用户将输入一个范围，然后检查范围的值"abc"在每一行中，1然后将其添加到数组中，然后使用列号的数组值删除整个列，下面的代码有一个错误，说类型不匹配，说x为空。

Option Explicit
Sub delete_column()
Dim arr As Variant, x As Integer, myrange As String, rng As Range, cell, 
item as Variant
Set arr = CreateObject("System.Collections.ArrayList")
myrange = InputBox("Please enter the range:", "Range")
If myrange = "" Then Exit Sub
Set rng = Range(myrange)
For Each cell In rng
If cell.Value = "abc" Then arr.Add cell.Column
Next cell
For x = UBound(arr) To LBound(arr)
Cells(1, arr(x)).EntireColumn.Delete
Next x
End Sub

我想不出如何修复这个错误。当我将鼠标悬停在x上时，错误显示类型不匹配后，它显示为空。当我将鼠标悬停在arr(x)上时，它有正确的列号。

我用For x = UBound(arr) To LBound(arr) Step -1still error

我在下面使用，但它只删除了一些列，而不是所有的值都是abc。

对于arr中的每个项目.EntireColumn.Delete细胞(1项)下一个项目

Answer 1

您不能在变体上使用Ubound()和Lbound()方法。如果要使用数组，则必须用括号声明arr。下面我将arr声明为一个整数数组。

Option Explicit
Sub delete_column()
Dim arr() As Integer, x As Integer, myrange As String, rng As Range, item As Variant, cell

myrange = InputBox("Please enter the range:", "Range")
If myrange = "" Then
Exit Sub
End If
Set rng = Range(myrange)

For Each cell In rng
If cell.Value = "abc" Then
If (Not Not arr) = 0 Then'check if arr is not dimensioned
ReDim arr(0) 'dimension the array
arr(UBound(arr)) = cell.Column
Else
ReDim Preserve arr(UBound(arr) + 1)'expand the array by 1
arr(UBound(arr)) = cell.Column
End If
End If
Next cell
For x = UBound(arr) To LBound(arr) Step -1
Cells(1, arr(x)).EntireColumn.Delete
Next x
End Sub

Answer 2

我使用。count找到了下面的解决方案，我不知道为什么使用inbound和lbound迭代不起作用

"

Option Explicit
Sub delete_column()
Dim arr As Variant, x As Integer, myrange As String, rng As Range, cell
Set arr = CreateObject("System.Collections.ArrayList")
myrange = InputBox("Please enter the range:", "Range")
If myrange = "" Then Exit Sub
Set rng = Range(myrange)
For Each cell In rng
If cell.Value = "abc" Then arr.Add cell.Column
Next cell
For x = arr.Count - 1 To 0 Step -1
Cells(1, arr(x)).EntireColumn.Delete
Next
End Sub

"