deleting user input column range using an array-CodePudding

hi im trying to learn using arrays, below code is a user will input a range then check the range for a value "abc" in each row 1 then add it to an array then with the array values of column numbers delete the entire column, below code has an error saying type mismatch saying x is empty

  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

i cant figure out how to fix the error, after the error shows of type mismatch when i hover my mouse to the x it says empty when i hover my mouse to the arr(x) it has the correct column number

i used 'For x = UBound(arr) To LBound(arr) Step -1' still error

i used below but it only deleted some of the columns not all with values of abc

For Each item In arr Cells(1, item).EntireColumn.Delete Next item

CodePudding user response：

i found the solution below using .count, i dont know why iteration with ubound and lbound did not work

'''

  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

'''

CodePudding user response：

You can't use the methods Ubound() and Lbound() on a variant. If you want to use an array, you have to declare arr with parenthesis. Below I've declared arr as an array of integers.

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