Delete Every Column After

[Michael Smith]

I need to Delete Every Column in my worksheet after the column titled
"SALES", which obviously is in a different column every time I get one
of these worksheets. How would I write the code to find my "SALES"
column and delete every column after it?

TIA - mike



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

Is that key always in the same row (row 1???):

Option Explicit
Sub testme01()
Dim FoundCell As Range
Dim wks As Worksheet
Dim LookFor As String

Set wks = ActiveSheet
LookFor = "Sales"

With wks
With .Rows(1) 'row 1
Set FoundCell = .Find(what:=LookFor, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlNext, _
searchdirection:=xlByRows, MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox LookFor & " wasn't found!"
Else
If FoundCell.Column < .Columns.Count Then
.Range(FoundCell.Offset(0, 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End If
End With

End Sub

I looked for Sales in a cell by itself (xlwhole)--and I didn't care about the
case (Upper/lower/mixed (matchcase:=false)).

 

[Michael Smith]

Yes it was in row 1, and thank you... that worked perfectly.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!