Solved

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

Posted on 2013-01-10
9
420 Views
Last Modified: 2013-01-28
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
Comment
Question by:simsima_7876
  • 4
  • 3
  • 2
9 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 38764192
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
 

Author Comment

by:simsima_7876
ID: 38764243
I get a error 2042 with this code even though the value is definitely there.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38764267
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
 

Author Comment

by:simsima_7876
ID: 38764269
However in this case the value I am searching for is 41723 ( a numeric date) as opposed to the string Old_address
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:redmondb
ID: 38764289
Apologies, simsima_7876, I forgot to skip columns A:B. Please see attached.Delete-Columns-V2.xls
0
 
LVL 33

Expert Comment

by:Norie
ID: 38764314
simsima

Did you change the code I posted in anyway?
0
 
LVL 26

Accepted Solution

by:
redmondb earned 400 total points
ID: 38764341
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
 

Author Closing Comment

by:simsima_7876
ID: 38826970
Hi Redmond - sorry for the delay.  You arethe man!  Thanks for the solution - it worked like  a dream.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38827029
Thanks, simsima!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel Error per WinZip Add-In 9 32
Excel : show empty cell if zero 7 22
Convert .PDF 6 43
onOpen 14 43
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now