Delete Columns to the left and above a column name

[zalik22]

Hi,

I have a cell labeled "Agent Group" somewhere in my file and would like to delete everything above and to the left of it. Any help on how to do this?

Thanks!

 

[Fluff]

Re: Delete Columns to the left and above a coulmn name

Do you mean delete entire rows & columns, so that Agent Group becomes cell A1?

 

[zalik22]

Re: Delete Columns to the left and above a coulmn name

Yes please!

 

[Joe4]

Re: Delete Columns to the left and above a coulmn name

Here is some code that should do that:

Sub MyMacro()

    Dim fCell As Range

'   Find Agent Group
    On Error GoTo err_chk
    Cells.Find(What:="Agent Group", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    Set fCell = ActiveCell
    
'   Delete rows above
    If fCell.Row > 1 Then
        Rows("1:" & fCell.Row - 1).Delete
    End If
    
'   Delete columns to the left
    If fCell.Column > 1 Then
        Range(Cells(1, 1), Cells(1, fCell.Column - 1)).EntireColumn.Delete
    End If
    
    Exit Sub
    
err_chk:
    MsgBox "Cannot find the text 'Agent Group' on sheet", vbOKOnly, "ERROR!"
    
End Sub