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!

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

"CFitz" wrote:

> 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!

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" wrote:

> 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!

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)

"JLGWhiz" wrote:

> 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
>
> "CFitz" wrote:
>
> > 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!

Still getting the same error as above.

"JLGWhiz" wrote:

> 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" wrote:
>
> > 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!

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

--
Regards,
Tom Ogilvy


"CFitz" wrote:

> 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)
>
> "JLGWhiz" wrote:
>
> > 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
> >
> > "CFitz" wrote:
> >
> > > 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!

Thanks!

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "CFitz" wrote:
>
> > 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)
> >
> > "JLGWhiz" wrote:
> >
> > > 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
> > >
> > > "CFitz" wrote:
> > >
> > > > 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!

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

"CFitz" wrote:

> Still getting the same error as above.
>
> "JLGWhiz" wrote:
>
> > 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" wrote:
> >
> > > 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!