Ok so I have a long list of data. I have 32 lines of good stuff an
then 10 bad lines, repeating for around 30000 lines. I need to remov
the 10 bad lines. I am trying to do it by looking to see if the cel
is empty or has a cetain value in it and then removes the whole row.
It doesn't run. Help please.

Sub Trash()
Dim myCell As Range, rng As Range, RW As Range
Set rng = Range("C9:C28870")

For Each myCell In rng


If IsEmpty(myCell) Then
RW = myCell.row
Rows(RW).Select
Selection.Delete Shift:=xlUp
End If

If Not IsEmpty(myCell) And myCell.Value = "grp / transactionhisto
Then
RW = myCell.row
Rows(RW).Select
Selection.Delete Shift:=xlUp
End If

Next myCell
End Su

--
Message posted from http://www.ExcelForum.com

Hi !

Two thing go wrong here. First you are declaring RW as Range, while it
actually is an integer (Long integer). The second problem is that the range
you have defined gets smaller with each row you're deleting.

The following code should do the trick:


Sub Trash()

Dim Row As Long
Dim EndRow As Long

Row = 9
EndRow = 28870

Do While Row <= EndRow

If IsEmpty(Cells(Row, 3)) Then
Rows(Row).Select
Selection.Delete Shift:=xlUp
'Subtract 1 from Row and EndRow for each row that is deleted
Row = Row - 1
EndRow = EndRow - 1
ElseIf Not IsEmpty(Cells(Row, 3)) And Cells(Row, 3).Value = "grp /
transactionhisto" Then
Rows(Row).Select
Selection.Delete Shift:=xlUp
'Subtract 1 from Row and EndRow for each row that is deleted
Row = Row - 1
EndRow = EndRow - 1
End If

Row = Row + 1
Loop

End Sub


Good luck !


"Spork Rhonewood " wrote in
message ...
Ok so I have a long list of data. I have 32 lines of good stuff and
then 10 bad lines, repeating for around 30000 lines. I need to remove
the 10 bad lines. I am trying to do it by looking to see if the cell
is empty or has a cetain value in it and then removes the whole row.
It doesn't run. Help please.

Sub Trash()
Dim myCell As Range, rng As Range, RW As Range
Set rng = Range("C9:C28870")

For Each myCell In rng


If IsEmpty(myCell) Then
RW = myCell.row
Rows(RW).Select
Selection.Delete Shift:=xlUp
End If

If Not IsEmpty(myCell) And myCell.Value = "grp / transactionhisto"
Then
RW = myCell.row
Rows(RW).Select
Selection.Delete Shift:=xlUp
End If

Next myCell
End Sub


---
Message posted from http://www.ExcelForum.com/