• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Loop through a row and delete all columns when a certain value is found

Hi Experts

I need to be able to loop through the column headings in Row 1 of a spreadsheet (starting from column C  i.e "C1" and find the first column which contains value "Old_Address" and then delete this columns and all columns  after this.  So for example if "Old_Address" was found in Cell H1 then I would want all the columns from H onwards to be deleted.  The number of columns will change everyday.

Your assistance would be appreciated.

Thanks
0
simsima_7876
Asked:
simsima_7876
  • 4
  • 3
  • 2
1 Solution
 
NorieVBA ExpertCommented:
Try this.
Dim varOldAdd As Variant
Dim LastCol As Long

    varOldAdd = Application.Match("Old_Address", Rows(1), 0)
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    If Not IsError(varOldAdd) Then
        Cells(1, varOldAdd).Resize(, LastCol - varOldAdd + 1).EntireColumn.Delete
    End If

Open in new window

0
 
simsima_7876Author Commented:
I get a error 2042 with this code even though the value is definitely there.
0
 
redmondbCommented:
Hi,  simsima_7876.

Please see attached. The code is...
Option Explicit

Sub Delete_Columns()
Dim xCol      As Range
Dim xLast_Col As Long
Dim i         As Long

Sheets("To Be Deleted").Activate

If ActiveSheet.UsedRange.Columns.Count > 0 Then xLast_Col = [A1].SpecialCells(xlLastCell).Column
If xLast_Col < 4 Then
    MsgBox ("No columns after C - run cancelled.")
    Exit Sub
End If

Set xCol = Range("1:1").Find(What:="Old_Address", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If xCol Is Nothing Then
    MsgBox ("""Old_Address"" not found.")
Else
    Range(Cells(1, xCol.Column), Cells(1, xLast_Col)).EntireColumn.Delete
    If ActiveSheet.UsedRange.Columns.Count < 0 Then Debug.Print "?!"
End If

End Sub

Open in new window

Regards,
Brian.
Delete-Columns.xls
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
simsima_7876Author Commented:
However in this case the value I am searching for is 41723 ( a numeric date) as opposed to the string Old_address
0
 
redmondbCommented:
Apologies, simsima_7876, I forgot to skip columns A:B. Please see attached.Delete-Columns-V2.xls
0
 
NorieVBA ExpertCommented:
simsima

Did you change the code I posted in anyway?
0
 
redmondbCommented:
simsima,

The attached handles dates. The code is...
Option Explicit

Sub Delete_Columns()
Dim xCol      As Range
Dim xLast_Col As Long
Dim i         As Long
Dim xSearch   As Variant

Sheets("To Be Deleted").Activate

If ActiveSheet.UsedRange.Columns.Count > 0 Then xLast_Col = [A1].SpecialCells(xlLastCell).Column
If xLast_Col < 3 Then
    MsgBox ("No columns after B - run cancelled.")
    Exit Sub
End If

xSearch = DateSerial(2014, 3, 25)

Set xCol = Range(Cells(1, 3), Cells(1, xLast_Col)).Find(What:=xSearch, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
If xCol Is Nothing Then
    MsgBox (xSearch & " not found.")
Else
    Range(Cells(1, xCol.Column), Cells(1, xLast_Col)).EntireColumn.Delete
    If ActiveSheet.UsedRange.Columns.Count < 0 Then Debug.Print "?!"
End If

End Sub

Open in new window

Regards,
Brian.Delete-Columns-V3.xls
0
 
simsima_7876Author Commented:
Hi Redmond - sorry for the delay.  You arethe man!  Thanks for the solution - it worked like  a dream.
0
 
redmondbCommented:
Thanks, simsima!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now