Delete columns based on name, loop through list

[imeade]

Hello,

I have a "Data" sheet and a "Reference" sheet. On the data sheet I want to delete specific columns based on the header name. The following code I have below looks up the named column based on a value on the "Reference" sheet in column E. Right now the code is a specific reference, what I want to do is create a loop that will first look at cell E2, delete the column on "Data", then move on to E3, delete the column, then move on to E4 and so forth. How can I layer in a loop on a specific range in column E on the "Reference" sheet.


Sub columns()

Dim delColumn1 As Range
Dim delColumn2 As Range

Set delColumn1 = Sheets("Reference").Range("e2")
Set delColumn2 = Sheets("Reference").Range("e3")

Sheets("Data").Select

Set ColumnName = Rows(1).Find(delColumn1, , xlValues, xlWhole)
If Not ColumnName Is Nothing Then
ColumnName.EntireColumn.Delete
End If

Set ColumnName = Rows(1).Find(delColumn2, , xlValues, xlWhole)
If Not ColumnName Is Nothing Then
ColumnName.EntireColumn.Delete
End If


End Sub


Thanks!

 

[dmt32]

Hi,
see if this update to your code will do what you want

Sub DeleteColumns()
    
    Dim delColumn   As Range, cell As Range
    
    Set delColumn = Worksheets("Reference").Range("e2:e3")
    
    For Each cell In delColumn.Cells
        If Len(cell.Value) > 0 Then
            Set ColumnName = Worksheets("Data").Rows(1).Find(cell.Value, , xlValues, xlWhole)
            If Not ColumnName Is Nothing Then ColumnName.EntireColumn.Delete
        End If
        Set ColumnName = Nothing
    Next cell
    
End Sub

Change the list range address shown in red as required

Dave

 

[imeade]

Hi,
see if this update to your code will do what you want

Sub DeleteColumns()
   
    Dim delColumn   As Range, cell As Range
   
    Set delColumn = Worksheets("Reference").Range("e2:e3")
   
    For Each cell In delColumn.Cells
        If Len(cell.Value) > 0 Then
            Set ColumnName = Worksheets("Data").Rows(1).Find(cell.Value, , xlValues, xlWhole)
            If Not ColumnName Is Nothing Then ColumnName.EntireColumn.Delete
        End If
        Set ColumnName = Nothing
    Next cell
   
End Sub

Change the list range address shown in red as required

Dave

Perfect, thanks Dave!

 

[dmt32]

Perfect, thanks Dave!

most welcome & appreciate feedback

Dave

 

[imeade]

most welcome & appreciate feedback

Dave

Hi Dave - I have another similiar question/thread and was wondering if you could assist:

VBA to autofilter on a certain column, copy and paste into new sheet, loop through list

Hello, I currently have a macro that does the following: Two ranges named on the [Reference] worksheet Based on named range "LenderCode" on the [Reference] worksheet, filter on the column "Reference-2" on the [Data] worksheet for the named range Copy filtered data and paste into new worksheet...

www.mrexcel.com

Thanks
Ian

 

[dmt32]

Hi Dave - I have another similiar question/thread and was wondering if you could assist:

VBA to autofilter on a certain column, copy and paste into new sheet, loop through list

Hello, I currently have a macro that does the following: Two ranges named on the [Reference] worksheet Based on named range "LenderCode" on the [Reference] worksheet, filter on the column "Reference-2" on the [Data] worksheet for the named range Copy filtered data and paste into new worksheet...

www.mrexcel.com

Thanks
Ian

You should start a new thread - you can provide link back to this thread if think it will help forum

Dave