VBA Loop to Delete Multiple Columns

Question

I want to search/loop through all the columns headers located on row 1 of the opened file and delete it if it matches dColumns, which is a list of columns I do not needed and I put in a range.

Sub LLextract()

'Last cell in column
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set WS = ThisWorkbook.Worksheets("Consolidated Data")
With WS
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    LastCellRowNumber = LastCell.Row + 0
End With

Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant

'Set source workbook
Set wb = ActiveWorkbook

'Open the target workbook
vFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
      "Select a CSV file", , False)

'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile

'Set selectedworkbook
Set wb2 = ActiveWorkbook

Dim dColumns As Range
Set dColumns = wb.Worksheets("LL Columns to Delete").Range("A:A")

    Dim i As Integer
    Dim A As Range
    For i = 94 To 1 Step -1
        Set A = wb2.Cells(1, i)
        If wb2.Cells(1, i) = dColumns Then A.EntireColumn.Delete
    Next i

'wb2.Worksheets(1).Range("A1").Select

End Sub

Set dColumns = wb.Worksheets("LL Columns to Delete").Range("A") — ASE Dev
– ASE Dev, Commented May 25, 2016 at 17:43

BruceWayne · Accepted Answer · 2016-05-25 17:44:03Z

3

You can't do just Range("A"), replace that with Range("A:A").

(But what are you trying to do with dColumns?)

answered May 25, 2016 at 17:44

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

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

8 Comments

BruceWayne Over a year ago

@AnthonyS.Erdenetuguldur - I haven't run it, but from looking at it, does it work? Can you use Find with a column reference? It's looking in Cells(i,1) for ..."Column A"? But anyway - did changing the range fix the issue?

BruceWayne Over a year ago

@AnthonyS.Erdenetuguldur - Yeah, that's what I was thinking. In your OP, can you clarify what you're expecting to find in Cells(1,i)? Perhaps post some sample data?

BruceWayne Over a year ago

@AnthonyS.Erdenetuguldur - Cells(1,i) is a single cell, not an area to search. Do you expect Cells(1,i) to have A in it, then we know to delete column A?

Scott Craner Over a year ago

@AnthonyS.Erdenetuguldur the value that you are putting in dColumns in what cell is it? I think you want wb.Worksheets("LL Columns to Delete").Range("A1") or some specific cell, with the value to find. Or you have your Find fields backwards.

Scott Craner Over a year ago

@AnthonyS.Erdenetuguldur you are looping trough the wrong things. You need to loop through dColumn and use each individual cell value in your search of row 1 so say we do For Each rng in dColumn then our find would be Set rngfind = wb2.Range("1:1").Find(dcolumn then you would Columns(rngfind.Column).delete

|

ASE Dev · Accepted Answer · 2016-05-25 20:41:25Z

0

I solved it by just deleting the column when I open wb2. This question is no longer need to be answered or solved.

answered May 25, 2016 at 20:41

ASE Dev

5722 gold badges6 silver badges15 bronze badges

Collectives™ on Stack Overflow

VBA Loop to Delete Multiple Columns

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related