Delete all rows up to a certain number

[tonywatsonhelp]

Hi Everyone,
I have a sheet and have data in column A:G in AA1 i have a number (i.e.1100)
now this number represents a number in Column A (Not the row number)
so what i want is a macro that can delete all rows from two to the number in AA1
please help if you can
Thanks
Tony

 

[Norie]

Try something like this.

Range("A2:A" & Range("AA1").Value).Resize(, 7).Delete xlShiftUp

 

[maabadi]

Try this:

Sub DeleteRows()
Dim i As Long, Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
i = Application.WorksheetFunction.Match(Range("AA1"), Range("A2:A" & Lr), 0)
Range("A2:A" & i).Resize(, 7).Delete xlShiftUp
End Sub

 

[Fluff]

Another option

Sub tonywatson()
   Range("A2", Range("A:A").Find(Range("AA1").Value, , , xlWhole, , , False, , False)).EntireRow.Delete
End Sub