Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Help with error 91 please
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 |
#3
|
|||
|
|||
Help with error 91 please
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 |
#4
|
|||
|
|||
Help with error 91 please
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 |
#5
|
|||
|
|||
Help with error 91 please
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|