Delete Columns between Two Specific Columns (based on column heading name)

[jwb1012]

Hello, I have a unique situation where I need to delete the columns between two columns. The number of columns between these two will be different at various times... Column_B will always be the second column, but as columns are added in between, "Column_C" will push to the right


Here is my initial attempt... but it results in a run-time error 13 type mismatch. It highlights the bold/underlined line of code below.

Any thoughts? Thank you in advance for any guidance you may be able to provide.

Sub Res_Hrs_Cost()
    Dim Column_B As Integer
    Dim Column_C As Integer
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''        '(1.) DELETE COLUMNS BETWEEN START & END
        Set Dest_Sh = Sheets("SHEET 1")
        [I][U][B]Column_B = Dest_Sh.UsedRange.Find("Column B", , xlValues, xlWhole)[/B][/U][/I]
        Column_C = Dest_Sh.UsedRange.Find("Column C", , xlValues, xlWhole)
                If Column_C > 3 Then
                    Dest_Sh.Range(Cells(1, Column_B + 1), Cells(1, Column C - 1)).EntireColumn.Delete
                End If
                
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
              
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[Michael M]

Does this do what you require

Sub MM1()
Dim LC As Integer, C As Integer
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set Dest_Sh = Sheets("SHEET 1")
LC = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    For C = LC - 1 To 3 Step -1
        If Columns.Count <= 3 Then Exit Sub
        Columns(C).EntireColumn.Delete
    Next C
ExitTheSub:
Application.GoTo Dest_Sh.Cells(1)
ActiveWindow.DisplayGridlines = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

 

[jwb1012]

Hi Michael - thank you for the quick reply. It currently results in a run time error 1004: delete method of range failed, and highlights the line:

        Columns(C).EntireColumn.Delete

Also, I have additional columns to the right of "Column_C" so I'm not sure if this line will work:

        If Columns.Count <= 3 Then Exit Sub

I was thinking something along the lines of "If column_C minus column_B > 1 then..." might work better but I was unable to get that to work. Any other thoughts?

Thanks again!

 

[Michael M]

Ah....you didn't state this at the beginning

Also, I have additional columns to the right of "Column_C"

Do the columns have header names ??
If so what is Col B and Col C ?
Will the inserted cols have different names ??

 

[jwb1012]

On second attempt, it doesn't return the error I mentioned above. But, it doesn't give the result I am looking for - although it is VERY CLOSE!!

To give you a better idea, here is how the spreadsheet is laid out. Columns will periodically be added between Column_B and Column_C, so when I run this macro I need it to only delete the columns between the two.

Column A

Column B

<delete><delete>DELETE</delete></delete>

<delete><delete><delete></delete></delete> DELETE </delete>

Column C

Column D

Column E

Column F

…… +

<tbody>
</tbody>

When I run your code, it results in this:

Column A

Column B

…… +

<tbody>
</tbody>

I need it to result in this

Column A

Column B

Column C

Column D

Column E

Column F

…… +

<tbody>
</tbody>

<tbody>
</tbody>

 

[jwb1012]

Also, yes Column B is really called "Resource ID" and Column C is really called "Burden Pool"... and the columns between the two will have different names in each scenario. I was attempting to generalize a bit to make it easier. See below

Sub Res_Hrs_Cost()
    Dim Column_B As Integer
    Dim Column_C As Integer
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''        '(1.) DELETE COLUMNS BETWEEN COLUMN B (RESOURCE ID) AND COLUMN C (BURDEN POOL)
        Set Dest_Sh = Sheets("SHEET 1")
        [I][U][B]Column_B = Dest_Sh.UsedRange.Find("Resource ID", , xlValues, xlWhole)[/B][/U][/I]
        Column_C = Dest_Sh.UsedRange.Find("Burden Pool", , xlValues, xlWhole)
                If Column_C > 3 Then
                    Dest_Sh.Range(Cells(1, Column_B + 1), Cells(1, Column C - 1)).EntireColumn.Delete
                End If
                
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
              
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[Michael M]

Okay maybe this then...Make sure your sheet name really is "SHEET 1"

Sub MM1()
Dim LC As Integer, C As Integer
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
End With
LC = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set Dest_Sh = Sheets("SHEET 1")
    For C = LC To 2 Step -1
        If Cells(1, C).Value = "Burden Pool" And Cells(1, C - 1).Value <> "Resource ID" Then
        Columns(C - 1).EntireColumn.Delete
    End If
    Next C
ExitTheSub:
Application.GoTo Dest_Sh.Cells(1)
ActiveWindow.DisplayGridlines = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

 

[jwb1012]

Here is my attempt to adopt your initial logic. Unfortunately, I get a run-time error 13: type mismatch (line bolded/underlined). Any thoughts?

- columns will be added between resource ID (column B) and burden pool (Column C) with different names in each scenario
- when I run the macro I need the columns between resource ID (column B) and burden pool (Column C)
- there are additional columns beyond burden pool (column C)

Sub Res_Hrs_Cost()
    Dim Column_C As Integer
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set Dest_Sh = Sheets("Res Hrs Cost-PP")
[I][U][B]Column_C = Dest_Sh.UsedRange.Find("Burden Pool ID", , xlValues, xlWhole)[/B][/U][/I]
    For C = Column_C - 1 To 3 Step -1
        If Column_C = 3 Then Exit Sub
        Columns(C).EntireColumn.Delete
    Next C
                               
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[jwb1012]

Ah sorry! I missed your last post...let me give it a shot now!

Thanks again!

 

[jwb1012]

Amazing!!!! Thank you very very much. It worked!