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

J

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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.

Code: 
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
 
Sort by date Sort by votes
Does this do what you require

Code: 
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
 
Upvote 0
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:

Code: 
        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:

Code: 
        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!
 
Upvote 0
Ah....you didn't state this at the beginning

Code: 
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 ??
 
Last edited:
Upvote 0
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.



[TABLE="width: 570"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]<delete><delete>DELETE</delete></delete>
[/TD]
[TD]<delete><delete><delete></delete></delete>
DELETE
</delete>

[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]……
+
[/TD]
[/TR]
</tbody>[/TABLE]


When I run your code, it results in this:

[TABLE="width: 159"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]…… +
[/TD]
[/TR]
</tbody>[/TABLE]


I need it to result in this


[TABLE="width: 399"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]…… +
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 570"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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


Code: 
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
 
Last edited:
Upvote 0
Okay maybe this then...Make sure your sheet name really is "SHEET 1"
Code: 
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
 
Upvote 0
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)

Code: 
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
 
Last edited:
Upvote 0
Ah sorry! I missed your last post...let me give it a shot now!

Thanks again!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Delete Row if
Replies
6
Views
270
Eric Penfold
E
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
503
Moley84
M
O
  • Solved
Error in Macro request to delete all rows between two specific text values whose rows are dynamic
Replies
5
Views
132
Joe4
Joe4
B
  • Question Question
VBA to delete attachment created on desktop
Replies
4
Views
95
bh24524
B
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
688
Jiraya_00
J

Forum statistics

Threads
1,226,857
Messages
6,193,379
Members
453,792
Latest member
Vic001

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top