Delete column which starts with a value

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Do you mean a numeric value or just any value? And what row or rows are your headers in?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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