Delete All Rows Above Trigger

[CFitz]

I'm looking for a macro that will find the row that contains the word "Other"
and then delete every row above it. Unfortunately there isn't a specific
column to look in because columns are constantly being added and subtracted
from the file.

Thanks!

 

[JLGWhiz]

See if this works. I didn't test it.

Sub DelAllAbove()
Dim c As Range
Set c = ActiveWorkbook.ActiveSheet. _
Find("Other", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:A" & c.Row + 1).EntireRow.Delete
End If
End Sub

 

[JLGWhiz]

I had a typo in the first one. Should have been a minus sign.

Sub DelAllAbove()
Dim c As Range
Set c = ActiveWorkbook.ActiveSheet. _
Find("Other", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:A" & c.Row - 1).EntireRow.Delete
End If
End Sub

 

[CFitz]

It didn't work. I'm getting an error that says "Object doesn't support this
property or method" and it highlights

Set c = ActiveSheet. _
Find("Other", LookIn:=xlValues)

 

[CFitz]

Still getting the same error as above.

I had a typo in the first one. Should have been a minus sign.

Sub DelAllAbove()
Dim c As Range
Set c = ActiveWorkbook.ActiveSheet. _
Find("Other", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:A" & c.Row - 1).EntireRow.Delete
End If
End Sub

 

[Tom Ogilvy]

Sub DelAllAbove()
Dim c As Range
With ActiveSheet
Set c = .Cells _
.Find("Other", LookIn:=xlValues)
If Not c Is Nothing Then
If c.Row = 1 Then
.Rows(1).Delete
Else
.Range("A1:A" & c.Row - 1).EntireRow.Delete
End If
Else
MsgBox "Other not found"
End If
End With
End Sub

 

[CFitz]

Thanks!

Sub DelAllAbove()
Dim c As Range
With ActiveSheet
Set c = .Cells _
.Find("Other", LookIn:=xlValues)
If Not c Is Nothing Then
If c.Row = 1 Then
.Rows(1).Delete
Else
.Range("A1:A" & c.Row - 1).EntireRow.Delete
End If
Else
MsgBox "Other not found"
End If
End With
End Sub

 

[JLGWhiz]

Yeah, haste makes waste. Forgot the Cells. Without that it don't know where
to look. Tom caught it right off.