VBA Arrays to delete entire column

Question

This code is an attempt to delete columns that contain Header rows that match the “Text” in the Array (list).

The code successfully deletes the column based on the 1st array value "Header Text 1".

The problem occurs during the 2nd pass of the loop on A.EntireColumn.Delete. When I print the vItem, it displays the expected “Header Text 2” value, which is the correct item in the array.

VBA Error – Runtime error ‘91’

Object variable or With block variable not set

Sub ArrayLoop()
Dim ColumnsToRemove As Variant
Dim vItem As Variant
Dim A As Range

ColumnsToRemove = Array("Header Text 1", "Header Text 2", "Header Text 3")

For Each vItem In ColumnsToRemove

    Set A = Rows(1).Find(What:=(ColumnsToRemove), LookIn:=xlValues, lookat:=xlPart)
    Debug.Print vItem
    A.EntireColumn.Delete

Next
End Sub

You need to search for vitem I think

Nathan_Sav
– Nathan_Sav

2016-02-29 20:42:26 +00:00
Commented Feb 29, 2016 at 20:42 — Nathan_Sav
– Nathan_Sav, Commented Feb 29, 2016 at 20:42

Tim Williams · Accepted Answer · 2016-02-29 20:59:27Z

3

You need to look for vItem, and you need to add a check to see if it was found before trying to delete the column it's in.

Sub ArrayLoop()
Dim ColumnsToRemove As Variant
Dim vItem As Variant
Dim A As Range

ColumnsToRemove = Array("Header Text 1", "Header Text 2", "Header Text 3")

For Each vItem In ColumnsToRemove

    Set A = Rows(1).Find(What:=vItem, LookIn:=xlValues, _
                          lookat:=xlPart)

    Debug.Print vItem, Not A Is Nothing

    If Not A Is Nothing Then A.EntireColumn.Delete

Next
End Sub

answered Feb 29, 2016 at 20:59

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

3 Comments

BruceWayne Over a year ago

Just curious if you know any advantage or disadvantage, with an array, in using for each x in array instead of using the l- and ubound, as in my answer? Or is it just the same?

Tim Williams Over a year ago

@BruceWayne - not sure it matters: I used For Each here because that's what the OP had used. EDIT: you can't use For each if you want to update the array member in the loop. Not applicable here though.

BruceWayne Over a year ago

Thanks for the clarification on updating the array, and yeah I figured you just did that since OP did. Cheers!

BruceWayne · Accepted Answer · 2016-02-29 21:01:02Z

1

A way to loop through an index is to use ubound and lbound:

Sub ArrayLoop()
Dim ColumnsToRemove As Variant
Dim vItem As Variant
Dim A As Range
Dim i As Long

ColumnsToRemove = Array("Header Text 1", "Header Text 2", "Header Text 3")


For i = LBound(ColumnsToRemove) To UBound(ColumnsToRemove)
    Set A = Rows(1).Find(what:=ColumnsToRemove(i), LookIn:=xlValues, lookat:=xlPart)
    Debug.Print ColumnsToRemove(i) ' not sure what `vItem` was intended to be
If A Is Nothing Then
    Debug.Print "Nothing found"
ElseIf Not A Is Nothing Then
    A.EntireColumn.Delete
End If
Next i

End Sub

answered Feb 29, 2016 at 21:01

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

1 Comment

0m3r Over a year ago

Nice, looks like we post at the same time

0m3r · Accepted Answer · 2016-02-29 22:18:11Z

1

Sub ArraybLoop()
Dim ColumnsToRemove() As Variant
Dim vItem As Variant
Dim A As Range
Dim i As Long
Dim Sht As Worksheet

Set Sht = ActiveWorkbook.Sheets("Sheet1")

ColumnsToRemove = Array("Header Text 1", "Header Text 2", "Header Text 3")


    For i = LBound(ColumnsToRemove) To UBound(ColumnsToRemove) Step 1
        vItem = Application.Match(ColumnsToRemove(i), Sht.Rows(1), 0)

        Debug.Print vItem

        If IsNumeric(vItem) Then Sht.Columns(vItem).Delete
    Next i

End Sub

Upper and Lower Bounds

Understanding Arrays

edited Feb 29, 2016 at 22:18

answered Feb 29, 2016 at 21:00

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

1 Comment

BruceWayne Over a year ago

I like this - using Match removes the need to check for if there's no match, correct? Because for vItem to be numeric, you'd have to have a match, right? Clever! My way is a little more wordy :/

Collectives™ on Stack Overflow

VBA Arrays to delete entire column

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related