I have this following macro which I want to scan through all cells in
my worksheet and delete all rows that don't contain the keywords
"NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
error on "test = Application.Range(r,c).Text" It says "method Range of
object _Application failed" Does anyone know how to get the text from
each cell and compare it to the strings I have? Thanks

Sub DeleteR()
Dim bool As Boolean
For r = Application.Rows.Count To 1 Step -1
bool = False
For c = Application.Columns.Count To 1 Step -1
Dim test As String
test = Application.Range(r, c).Text
If test = "NAME:" Then
'mark bool as true (we found a table name row)
bool = True
End If
If test = "SUBJ:" Then
'mark bool as true (we found a subject area row)
bool = True
End If
Next c
If bool = False Then
'Delete entire row
Application.Rows(r).EntireRow.Delete
End If
Next r
End Sub

Chris,

You need to use "Cells" instead of "Range".
Range is just not designed to function the way you are using it.
So...
test = ActiveSheet.Cells(r, c).Text

There appears to be other issues with your code.
We will probably here from you again. <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Chris"
<(E-Mail Removed)>
wrote in message
I have this following macro which I want to scan through all cells in
my worksheet and delete all rows that don't contain the keywords
"NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
error on "test = Application.Range(r,c).Text" It says "method Range of
object _Application failed" Does anyone know how to get the text from
each cell and compare it to the strings I have? Thanks

Sub DeleteR()
Dim bool As Boolean
For r = Application.Rows.Count To 1 Step -1
bool = False
For c = Application.Columns.Count To 1 Step -1
Dim test As String
test = Application.Range(r, c).Text
If test = "NAME:" Then
'mark bool as true (we found a table name row)
bool = True
End If
If test = "SUBJ:" Then
'mark bool as true (we found a subject area row)
bool = True
End If
Next c
If bool = False Then
'Delete entire row
Application.Rows(r).EntireRow.Delete
End If
Next r
End Sub

As mentioned, you need to use cells(r,c) but if your strings to find are all
in the same column then
sub deleterows()
for i =1 to cells(rows.count,"a").end(xlup).row
if cells(i,"a")="NAME:" or cells(i,"a")="SUBJ:" then rows(i).delete
next i
end sub

If not in the same column, look in the vba help index for FINDNEXT. There is
a good example. There is no reason to look at EVERY cell.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Chris" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have this following macro which I want to scan through all cells in
> my worksheet and delete all rows that don't contain the keywords
> "NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
> error on "test = Application.Range(r,c).Text" It says "method Range of
> object _Application failed" Does anyone know how to get the text from
> each cell and compare it to the strings I have? Thanks
>
> Sub DeleteR()
> Dim bool As Boolean
> For r = Application.Rows.Count To 1 Step -1
> bool = False
> For c = Application.Columns.Count To 1 Step -1
> Dim test As String
> test = Application.Range(r, c).Text
> If test = "NAME:" Then
> 'mark bool as true (we found a table name row)
> bool = True
> End If
> If test = "SUBJ:" Then
> 'mark bool as true (we found a subject area row)
> bool = True
> End If
> Next c
> If bool = False Then
> 'Delete entire row
> Application.Rows(r).EntireRow.Delete
> End If
> Next r
> End Sub
>

Actually, that did the job, thanks! But what did you think was
wrong? It worked for 47k rows by 20 columns in a little under 5
minutes, probably not the most efficient, but it did work.

On Jun 1, 8:50 am, "Jim Cone" <jim.cone...@rcn.comXXX> wrote:
> Chris,
>
> You need to use "Cells" instead of "Range".
> Range is just not designed to function the way you are using it.
> So...
> test = ActiveSheet.Cells(r, c).Text
>
> There appears to be other issues with your code.
> We will probably here from you again. <g>
> --
> Jim Cone
> San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

You could approach it this way:

Sub DeleteRows()
Dim lastrow as Long, i as long
Dim rng as Range
With ActiveSheet
set rng = .UsedRange
lastrow = rng(rng.count).row

for i = lastrow to 1 step -1
if application.Countif(.rows(i),"*NAME:*") + _
application.Countif(.rows(i),"*SUBJ:*") = 0 then
.rows(i).Delete
end if
Next i
End With
End Sub

--
Regards,
Tom Ogilvy

"Chris" wrote:

> I have this following macro which I want to scan through all cells in
> my worksheet and delete all rows that don't contain the keywords
> "NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
> error on "test = Application.Range(r,c).Text" It says "method Range of
> object _Application failed" Does anyone know how to get the text from
> each cell and compare it to the strings I have? Thanks
>
> Sub DeleteR()
> Dim bool As Boolean
> For r = Application.Rows.Count To 1 Step -1
> bool = False
> For c = Application.Columns.Count To 1 Step -1
> Dim test As String
> test = Application.Range(r, c).Text
> If test = "NAME:" Then
> 'mark bool as true (we found a table name row)
> bool = True
> End If
> If test = "SUBJ:" Then
> 'mark bool as true (we found a subject area row)
> bool = True
> End If
> Next c
> If bool = False Then
> 'Delete entire row
> Application.Rows(r).EntireRow.Delete
> End If
> Next r
> End Sub
>
>

To the OP.

I don't see where Find or FindNext has any role to play in deleting rows
that don't contain some prescribed value (except in a most roundabout
fashion).

Maybe that will save you some time.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote:

> As mentioned, you need to use cells(r,c) but if your strings to find are all
> in the same column then
> sub deleterows()
> for i =1 to cells(rows.count,"a").end(xlup).row
> if cells(i,"a")="NAME:" or cells(i,"a")="SUBJ:" then rows(i).delete
> next i
> end sub
>
> If not in the same column, look in the vba help index for FINDNEXT. There is
> a good example. There is no reason to look at EVERY cell.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Chris" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have this following macro which I want to scan through all cells in
> > my worksheet and delete all rows that don't contain the keywords
> > "NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
> > error on "test = Application.Range(r,c).Text" It says "method Range of
> > object _Application failed" Does anyone know how to get the text from
> > each cell and compare it to the strings I have? Thanks
> >
> > Sub DeleteR()
> > Dim bool As Boolean
> > For r = Application.Rows.Count To 1 Step -1
> > bool = False
> > For c = Application.Columns.Count To 1 Step -1
> > Dim test As String
> > test = Application.Range(r, c).Text
> > If test = "NAME:" Then
> > 'mark bool as true (we found a table name row)
> > bool = True
> > End If
> > If test = "SUBJ:" Then
> > 'mark bool as true (we found a subject area row)
> > bool = True
> > End If
> > Next c
> > If bool = False Then
> > 'Delete entire row
> > Application.Rows(r).EntireRow.Delete
> > End If
> > Next r
> > End Sub
> >
>
>

Tom, Friday.
However, it COULD find the values>move the row to the next available row on
another sheet>delete what's left>move all the rows back. Not good but saves
a little bit of face. <G>

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:EDBE1B31-17CE-457B-9915-(E-Mail Removed)...
> To the OP.
>
> I don't see where Find or FindNext has any role to play in deleting rows
> that don't contain some prescribed value (except in a most roundabout
> fashion).
>
> Maybe that will save you some time.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Don Guillett" wrote:
>
>> As mentioned, you need to use cells(r,c) but if your strings to find are
>> all
>> in the same column then
>> sub deleterows()
>> for i =1 to cells(rows.count,"a").end(xlup).row
>> if cells(i,"a")="NAME:" or cells(i,"a")="SUBJ:" then rows(i).delete
>> next i
>> end sub
>>
>> If not in the same column, look in the vba help index for FINDNEXT. There
>> is
>> a good example. There is no reason to look at EVERY cell.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Chris" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I have this following macro which I want to scan through all cells in
>> > my worksheet and delete all rows that don't contain the keywords
>> > "NAME:" and that don't contain the keywords "SUBJ:" I keep getting an
>> > error on "test = Application.Range(r,c).Text" It says "method Range of
>> > object _Application failed" Does anyone know how to get the text from
>> > each cell and compare it to the strings I have? Thanks
>> >
>> > Sub DeleteR()
>> > Dim bool As Boolean
>> > For r = Application.Rows.Count To 1 Step -1
>> > bool = False
>> > For c = Application.Columns.Count To 1 Step -1
>> > Dim test As String
>> > test = Application.Range(r, c).Text
>> > If test = "NAME:" Then
>> > 'mark bool as true (we found a table name row)
>> > bool = True
>> > End If
>> > If test = "SUBJ:" Then
>> > 'mark bool as true (we found a subject area row)
>> > bool = True
>> > End If
>> > Next c
>> > If bool = False Then
>> > 'Delete entire row
>> > Application.Rows(r).EntireRow.Delete
>> > End If
>> > Next r
>> > End Sub
>> >
>>
>>