I get a type mismatch error on the code below at the IF line. Im trying to
pick out the rows that start with a certain text and delete all of the other
rows to shift cells up. I have it working now by selecting each cell and
evaluating it but it takes a long time to go through 1 or 2 thousand lines.
I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four and
Five, I just want the One and Two rows to stay. Can anyone offer any
suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Dim c As Range, MyRange As Range
For Each c In MyRange
If Left(c, 3) < "One" Or Left(c, 3) < "Two" Then
c.EntireRow.Delete
End If
Next

Mike F
"Kevin" wrote in message
...
I get a type mismatch error on the code below at the IF line. Im trying to
pick out the rows that start with a certain text and delete all of the
other
rows to shift cells up. I have it working now by selecting each cell and
evaluating it but it takes a long time to go through 1 or 2 thousand
lines.
I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four and
Five, I just want the One and Two rows to stay. Can anyone offer any
suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

another approach

Sub find_val()
Dim myrange As Range
Set myrange = Worksheets(1).Range("a1:a50") '
For Each cell In myrange
Select Case Left(cell.Value, 3)
Case "One"
Case "Two"
Case Else
cell.Select
Selection.EntireRow.Delete

End Select
Next
End Sub

Kevin wrote:
I get a type mismatch error on the code below at the IF line. Im trying to
pick out the rows that start with a certain text and delete all of the other
rows to shift cells up. I have it working now by selecting each cell and
evaluating it but it takes a long time to go through 1 or 2 thousand lines.
I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four and
Five, I just want the One and Two rows to stay. Can anyone offer any
suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Your next discovery will be that you are skipping rows.

you need to loop backward to use the approach you have chosen


assume you are making your check on the values in column C

Dim i as LOng, lastrow as Long
lastrow = cells(rows.count,3).End(xlup).Row
for i = lastrow to 2 step -1
s = lcase(left(cells(i,3),3))
if s < "one" and s < "two" then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy



"stevebriz" wrote:

another approach

Sub find_val()
Dim myrange As Range
Set myrange = Worksheets(1).Range("a1:a50") '
For Each cell In myrange
Select Case Left(cell.Value, 3)
Case "One"
Case "Two"
Case Else
cell.Select
Selection.EntireRow.Delete

End Select
Next
End Sub

Kevin wrote:
I get a type mismatch error on the code below at the IF line. Im trying to
pick out the rows that start with a certain text and delete all of the other
rows to shift cells up. I have it working now by selecting each cell and
evaluating it but it takes a long time to go through 1 or 2 thousand lines.
I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four and
Five, I just want the One and Two rows to stay. Can anyone offer any
suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Thanks a bunch the AND statement did the trick. Although it doesnt run any
faster.

Is there a way to do a replace function and have it delete the rows of
unwanted text?

"John Fuller" wrote:

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

I could use a dummy column like this
(this assumes you have a header row in row 1)

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
End Sub

or you could use find to search for all the three's, then four's, then five's

or you could use the autofilter

--
Regards,
Tom Ogilvy




"Kevin" wrote:

Thanks a bunch the AND statement did the trick. Although it doesnt run any
faster.

Is there a way to do a replace function and have it delete the rows of
unwanted text?

"John Fuller" wrote:

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Guess I should also remove that dummy column

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, col as Long
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
col = rng.column
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
columns(col).ClearContents
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

I could use a dummy column like this
(this assumes you have a header row in row 1)

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
End Sub

or you could use find to search for all the three's, then four's, then five's

or you could use the autofilter

--
Regards,
Tom Ogilvy




"Kevin" wrote:

Thanks a bunch the AND statement did the trick. Although it doesnt run any
faster.

Is there a way to do a replace function and have it delete the rows of
unwanted text?

"John Fuller" wrote:

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

This work great Tom!!! cut the time down a lot.

Thanks much for the help.

"Tom Ogilvy" wrote:

Guess I should also remove that dummy column

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, col as Long
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
col = rng.column
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
columns(col).ClearContents
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

I could use a dummy column like this
(this assumes you have a header row in row 1)

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Cells(2, "IV").End(xlToLeft)(1, 2)
Set rng1 = Cells(Rows.Count, rng.Column - 1).End(xlUp)(1, 2)
With Range(rng, rng1)
.Formula = "=if(and(left(A2,3)<""One"",Left(A2,3)<""Two""), na(),"""")"
On Error Resume Next
Set rng2 = .SpecialCells(xlFormulas, xlErrors)
End With
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If
End Sub

or you could use find to search for all the three's, then four's, then five's

or you could use the autofilter

--
Regards,
Tom Ogilvy




"Kevin" wrote:

Thanks a bunch the AND statement did the trick. Although it doesnt run any
faster.

Is there a way to do a replace function and have it delete the rows of
unwanted text?

"John Fuller" wrote:

Yeah, if you write the If statement like that, you might as well take
it out, because it will always evaluate to true.

If what you're wanting is for the code run if the cell isn't equal to
"One" or "Two", then use:
If Left(ActiveCell, 3) < "One" And Left(ActiveCell, 3) < "Two" Then


Robert Bruce wrote:
Roedd <<Kevin wedi ysgrifennu:

I get a type mismatch error on the code below at the IF line. Im
trying to pick out the rows that start with a certain text and delete
all of the other rows to shift cells up. I have it working now by
selecting each cell and evaluating it but it takes a long time to go
through 1 or 2 thousand lines. I'm trying to speed it up a little.
EX. If the first cell in each row starts with One, Two, Three, Four
and Five, I just want the One and Two rows to stay. Can anyone offer
any suggestions?
======================================
For Each c In MyRange
If Left(ActiveCell, 3) < "One" Or "Two" Then
ActiveCell.EntireRow.Delete
End If
Next
======================================
Thanks,
Kevin

Each clause of the or needs to be a complete test:

If Left(ActiveCell, 3) < "One" Or Left(ActiveCell, 3) < "Two" Then

However, I think you really need an AND!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.