ExcelMaker007
New Member
- Joined
- Jul 14, 2020
- Messages
- 31
How to Delete column which column header starts with a value
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
Source | Result | ||||
Wake1 | Wake2 | Task | Wake3 | Task | |
any | any | any | any | any | |
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
If Left(Cells(hr, c), 4) = "Wake" Then
If Left(Ucase(Cells(hr, c)), 4) = "WAKE" Then
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
Sub ExcelMaker()
With Range("3:3")
.Replace "Wake*", True, xlPart, , False, , False, False
.SpecialCells(xlConstants, xlLogical).EntireColumn.Delete
End With
End Sub
xlPart really should be xlWhole otherwise the code would change a heading like "Awaken" to "ATRUE", even though it would not delete the column.Another VBA option
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.more precise