We help IT Professionals succeed at work.

With Found Cell

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi, i have some code that finds a cell.

What i want to do is say With Found Cell, delete everything above it, so all rows above that cell

What code would i use?

Thanks
Seamus
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
1) Do you want to use VBA or Excel formulas?
2) What exactly is With Found Cell? Is it the result of a find? Is it the selected one? If it's a find, how did you find it? With VBA, or manually?
Please be a bit more explicit so we can help you better :)

Commented:
Something like this maybe?
Public Sub deleterowsabove()
Dim l As Long

For l = 1 To ActiveCell.Row - 1
    ActiveSheet.Rows(1).Delete
Next l

End Sub

Open in new window

It should be Rows(l). And you don't need to delete one by one. A simple:
Range("A1:A" & ActiveCell.Row - 1).EntireRow.Delete
would do it all at once.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Something like:
   Range("A1", FoundCell.Offset(-1)).EntireRow.Delete

Open in new window

Most Valuable Expert 2011
Top Expert 2011
Commented:
Safer option:
   With FoundCell
      .Worksheet.Range("A1", .Offset(-1)).EntireRow.Delete
   End With

Open in new window

Author

Commented:
Sorry was away there, Rorys was the one that worked for me.

Thanks Everyone

Seamus