Delete column which starts with a value

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Do you mean a numeric value or just any value? And what row or rows are your headers in?
 

ExcelMaker007

New Member
Joined
Jul 14, 2020
Messages
31
Yes, My row 3 is the columns ... And in that I have column headers like.... Wake1, Wake2 , Task, Wake3

I need to delete all the column which column header starts with Wake
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Try this code:
VBA Code:
Sub MyDeleteColumns()

    Dim hr As Long
    Dim lc As Long
    Dim c As Long
    
'   Designate which row has headers
    hr = 1
    
'   Find column with last header on header row
    lc = Cells(hr, Columns.Count).End(xlToLeft).Column
    
    Application.ScreenUpdating = False
    
'   Loop through all columns backwards, deleting ones with "Wake..."
    For c = lc To 1 Step -1
        If Left(Cells(hr, c), 4) = "Wake" Then
            Columns(c).Delete Shift:=xlToLeft
        End If
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
You can easily change the row you headers are on by changing what the "hr" variable is set to.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

with Power Query

SourceResult
Wake1Wake2TaskWake3Task
anyanyanyanyany

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transpose = Table.Transpose(Table.DemoteHeaders(Source)),
    Numbers = Table.AddColumn(Transpose, "Custom", each Text.Select([Column1], {"0".."9"})),
    RC = Table.RemoveColumns(Table.SelectRows(Numbers, each [Custom] = ""),{"Custom"}),
    Promote = Table.PromoteHeaders(Table.Transpose(RC), [PromoteAllScalars=true])
in
    Promote
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Also, if you wish to delete the columns, regardless of case (i.e. also want to delete it if it looks like "wake..." or "WAKE..."), then change this row:
VBA Code:
If Left(Cells(hr, c), 4) = "Wake" Then
to this:
VBA Code:
If Left(Ucase(Cells(hr, c)), 4) = "WAKE" Then
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

or for Wake
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transpose = Table.Transpose(Table.DemoteHeaders(Source)),
    Proper = Table.TransformColumns(Transpose,{{"Column1", Text.Proper, type text}}),
    DNContain = Table.SelectRows(Proper, each not Text.Contains([Column1], "Wake")),
    Promote = Table.PromoteHeaders(Table.Transpose(DNContain), [PromoteAllScalars=true])
in
    Promote
doesn't matter lower or upper case
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,918
Office Version
  1. 365
Platform
  1. Windows
Another VBA option
VBA Code:
Sub ExcelMaker()
   With Range("3:3")
      .Replace "Wake*", True, xlPart, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).EntireColumn.Delete
   End With
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
more precise
VBA Code:
Sub t()
    With Range("A3", Cells(3, Columns.Count).End(xlToLeft))
        .Replace "Wake*", ""
        .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    End With
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Another VBA option
xlPart really should be xlWhole otherwise the code would change a heading like "Awaken" to "ATRUE", even though it would not delete the column.

more precise
Also needs xlWhole specified in case another Find has previously been done with xlPart in which case a similar issue to the above would arise.
This code would of course also incorrectly delete any column that happened to be missing a heading in row 3.

Both codes would be more robust with something, say, On Error Resume Next in case there were no headers with "Wake*" (eg the code accidentally gets run a second time)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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
Top