Thread: Help with error 91 please

Hi,

I've searched all over this forum and the help files to figure out how to
fix the below macro which works up until it can no longer find the string and
then I get a run-time error 91 on the cells.find line:
Sub delregion()
Dim x As String
x = "region total"
Do While x = "region total"
Cells.Find(What:="region total").Activate
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
Loop
End Sub

TIA for your help.

Allan

This snippet is adapted from the VBA help screen for the find method; the
basic idea is to detect the NOTHING returned by Find before attempting to
process it:

With ActiveSheet.Cells
Set c = .Find(2, LookIn:=xlValues) ''substitute your FIND expression
If Not c Is Nothing Then
firstaddress = c.Address
Do
' whatever processing you need to do
Set c = .FindNext(c)
If (Not (c Is Nothing)) Then
If c.Address = firstaddress Then c = Nothing
End If
Loop While (Not (c Is Nothing))
End If
End With

--Bruce


"KonaAl" wrote:

> Hi,
>
> I've searched all over this forum and the help files to figure out how to
> fix the below macro which works up until it can no longer find the string and
> then I get a run-time error 91 on the cells.find line:
> Sub delregion()
> Dim x As String
> x = "region total"
> Do While x = "region total"
> Cells.Find(What:="region total").Activate
> ActiveCell.EntireRow.Select
> Selection.Delete Shift:=xlUp
> ActiveCell.EntireRow.Select
> Selection.Delete Shift:=xlUp
> Loop
> End Sub
>
> TIA for your help.
>
> Allan

Thanks for the reply, Bruce. Unfortunately I couldn't get this to work(I'm
new to editing VBA code). Let me describe what I'm doing and maybe there is a
different solution.

I'm creating a macro to format data to be used for a pivot table. This file
routinely exceeds 30k lines. The data is by "region" and at each change in
region there are hard coded subtotals which I need to delete as well as the
next row which has other extraneous data.

Thanks for any suggestions.

Allan

"bpeltzer" wrote:

> This snippet is adapted from the VBA help screen for the find method; the
> basic idea is to detect the NOTHING returned by Find before attempting to
> process it:
>
> With ActiveSheet.Cells
> Set c = .Find(2, LookIn:=xlValues) ''substitute your FIND expression
> If Not c Is Nothing Then
> firstaddress = c.Address
> Do
> ' whatever processing you need to do
> Set c = .FindNext(c)
> If (Not (c Is Nothing)) Then
> If c.Address = firstaddress Then c = Nothing
> End If
> Loop While (Not (c Is Nothing))
> End If
> End With
>
> --Bruce
>
>
> "KonaAl" wrote:
>
> > Hi,
> >
> > I've searched all over this forum and the help files to figure out how to
> > fix the below macro which works up until it can no longer find the string and
> > then I get a run-time error 91 on the cells.find line:
> > Sub delregion()
> > Dim x As String
> > x = "region total"
> > Do While x = "region total"
> > Cells.Find(What:="region total").Activate
> > ActiveCell.EntireRow.Select
> > Selection.Delete Shift:=xlUp
> > ActiveCell.EntireRow.Select
> > Selection.Delete Shift:=xlUp
> > Loop
> > End Sub
> >
> > TIA for your help.
> >
> > Allan

Give this a go; it seems to be doing for me what you described. --BP

Sub delregion()
Dim x As String
Dim c As Variant

x = "region total"
Do
Set c = ActiveSheet.Cells.Find(x, LookIn:=xlValues)
If c Is Nothing Then Exit Do
Rows(c.Row & ":" & (c.Row + 1)).Select
Selection.Delete shift:=xlUp
Loop While (Not (c Is Nothing))

End Sub
------------------


"KonaAl" wrote:

> Thanks for the reply, Bruce. Unfortunately I couldn't get this to work(I'm
> new to editing VBA code). Let me describe what I'm doing and maybe there is a
> different solution.
>
> I'm creating a macro to format data to be used for a pivot table. This file
> routinely exceeds 30k lines. The data is by "region" and at each change in
> region there are hard coded subtotals which I need to delete as well as the
> next row which has other extraneous data.
>
> Thanks for any suggestions.
>
> Allan
>
> "bpeltzer" wrote:
>
> > This snippet is adapted from the VBA help screen for the find method; the
> > basic idea is to detect the NOTHING returned by Find before attempting to
> > process it:
> >
> > With ActiveSheet.Cells
> > Set c = .Find(2, LookIn:=xlValues) ''substitute your FIND expression
> > If Not c Is Nothing Then
> > firstaddress = c.Address
> > Do
> > ' whatever processing you need to do
> > Set c = .FindNext(c)
> > If (Not (c Is Nothing)) Then
> > If c.Address = firstaddress Then c = Nothing
> > End If
> > Loop While (Not (c Is Nothing))
> > End If
> > End With
> >
> > --Bruce
> >
> >
> > "KonaAl" wrote:
> >
> > > Hi,
> > >
> > > I've searched all over this forum and the help files to figure out how to
> > > fix the below macro which works up until it can no longer find the string and
> > > then I get a run-time error 91 on the cells.find line:
> > > Sub delregion()
> > > Dim x As String
> > > x = "region total"
> > > Do While x = "region total"
> > > Cells.Find(What:="region total").Activate
> > > ActiveCell.EntireRow.Select
> > > Selection.Delete Shift:=xlUp
> > > ActiveCell.EntireRow.Select
> > > Selection.Delete Shift:=xlUp
> > > Loop
> > > End Sub
> > >
> > > TIA for your help.
> > >
> > > Allan

That worked great! Thanks Bruce!

Allan

"bpeltzer" wrote:

> Give this a go; it seems to be doing for me what you described. --BP
>
> Sub delregion()
> Dim x As String
> Dim c As Variant
>
> x = "region total"
> Do
> Set c = ActiveSheet.Cells.Find(x, LookIn:=xlValues)
> If c Is Nothing Then Exit Do
> Rows(c.Row & ":" & (c.Row + 1)).Select
> Selection.Delete shift:=xlUp
> Loop While (Not (c Is Nothing))
>
> End Sub
> ------------------
>
>
> "KonaAl" wrote:
>
> > Thanks for the reply, Bruce. Unfortunately I couldn't get this to work(I'm
> > new to editing VBA code). Let me describe what I'm doing and maybe there is a
> > different solution.
> >
> > I'm creating a macro to format data to be used for a pivot table. This file
> > routinely exceeds 30k lines. The data is by "region" and at each change in
> > region there are hard coded subtotals which I need to delete as well as the
> > next row which has other extraneous data.
> >
> > Thanks for any suggestions.
> >
> > Allan
> >
> > "bpeltzer" wrote:
> >
> > > This snippet is adapted from the VBA help screen for the find method; the
> > > basic idea is to detect the NOTHING returned by Find before attempting to
> > > process it:
> > >
> > > With ActiveSheet.Cells
> > > Set c = .Find(2, LookIn:=xlValues) ''substitute your FIND expression
> > > If Not c Is Nothing Then
> > > firstaddress = c.Address
> > > Do
> > > ' whatever processing you need to do
> > > Set c = .FindNext(c)
> > > If (Not (c Is Nothing)) Then
> > > If c.Address = firstaddress Then c = Nothing
> > > End If
> > > Loop While (Not (c Is Nothing))
> > > End If
> > > End With
> > >
> > > --Bruce
> > >
> > >
> > > "KonaAl" wrote:
> > >
> > > > Hi,
> > > >
> > > > I've searched all over this forum and the help files to figure out how to
> > > > fix the below macro which works up until it can no longer find the string and
> > > > then I get a run-time error 91 on the cells.find line:
> > > > Sub delregion()
> > > > Dim x As String
> > > > x = "region total"
> > > > Do While x = "region total"
> > > > Cells.Find(What:="region total").Activate
> > > > ActiveCell.EntireRow.Select
> > > > Selection.Delete Shift:=xlUp
> > > > ActiveCell.EntireRow.Select
> > > > Selection.Delete Shift:=xlUp
> > > > Loop
> > > > End Sub
> > > >
> > > > TIA for your help.
> > > >
> > > > Allan