Need to Delete rows above the find - VBA Assistance Requested

[Randy11]

Code below finds "Data Start" in column 3 & deletes the row found in & all those above this point. Need to leave the found line & delete those above this point only. Must be getting late... Assistance appreciated.

Sub Delete_Stuff_Above_Line()
'

Dim rng As Range
Sheets("Import_Data").Select
Set rng = Columns(3).Find("Data Start", Cells(3, 3))
If Not rng Is Nothing Then
Range(Cells(1, 1), rng).EntireRow.Delete
End If
End Sub

 

[Hoaokapohaku]

This will work. You might need to put some error handling back in.

Sub Delete_Stuff_Above_Line()
'

'Dim rng As Range
Dim MyRow As Integer
Sheets("Import_Data").Select
MyRow = Columns(3).Find(What:="Data Start", After:=Cells(3, 3)).Row - 1
Rows("1:" & MyRow).Delete Shift:=xlUp
'If Not rng Is Nothing Then
'Range(Cells(1, 1), rng).EntireRow.Delete
'End If
End Sub

 

[SkipVought]

Sub Delete_Stuff_Above_Line()
'

Dim rng As Range
with Sheets("Import_Data")
    Set rng = .Columns(3).Find("Data Start", Cells(3, 3))
    If Not rng Is Nothing Then[b]
       Range(.Cells(1, 1), rng.offset(-1)).EntireRow.Delete[/b]
    End If
End with
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Thanks Both for the assistance. Skip, just removed the . before Cells & it worked like a charm.

 

[SkipVought]

What you did ONLY if that sheet is ACTIVE.

I almost NEVER use Activate and Select Methods!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Excellent point Skip, this is more efficient.... Adopted this approach, will try in other scenarios as well. Will particularly play close attention to code that either runs slow or poorly.