CODEWITHSUNDEEP
excelvba
k1dr0ck
k1dr0ck

Reputation: 1215

deleting user input column range using an array

I'm 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 can't 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

Upvotes: 0

Views: 67

Answers (2)

exceptional exception
exceptional exception

Reputation: 527

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

Upvotes: 0

k1dr0ck
k1dr0ck

Reputation: 1215

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

'''

Upvotes: 0

Related Questions