Hello,

I have a macro that does a lot of things, but I can't get it to work due to
syntax errors. This wa sa cut and past from a newsgroup.
any help?

Here was original request of what I was trying to do.

I would like to know how I can write a macro that will do the following:

1. I have 3 columns in the spreadsheet that have numbers that range from 1
to 99. If any one of the 3 numbers in the row are less than 70, then delete
the row.

2. I have 3 columns in the spreadsheet that have letters that range from A
to E. If any one of the 3 letters equal D or E then I want to delete the
complete row.

3. How can I sort the data once it goes through the above macros to remove
any duplicate data based on the second column? ie: if it is a repeat symbol
like AAPL delete the row. I do have a header row on row 1.

4. How can I sort based on a number in that column? If the number is less
than 100, then delete the row.

Thanks
--
Mike B

Here is the code


Sub DelRows()
Dim R&, C&, E&
C = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'Sort on Second Column
'Question #3 Part 1
Cells(1, 1).Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Loop Through Each Row
For R = 2 To C
'Prevent endless loop
E = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
If R E Then Exit For
'Delete Rows where Columns 3, 4, & 5 are < 70
'Question #1
If Cells(R, 3) < 70 Or Cells(R, 4) < 70 Or Cells(R, 5) < 70 Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Delete Rows where Columns 6, 7, & 8 are = "D"
'Question #2
If Cells(R, 6) = "D" Or Cells(R, 7) = "D" Or Cells(R, 8) = "D" Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Delete Rows where Columns 9 is less than 100
'Question #4
If Cells(R, 9) < 100 Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Remove Dublicates in Column 2
'Question #3 Part 2
If Cells(R, 2) = Cells(R - 1, 2) Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
Next R
Cells(1, 1).Select
End Sub

--
Mike B

from the bottom up something like

Sub sortanddelete()
ActiveSheet.UsedRange.Sort Key1:=Range("b2"), _
Order1:=xlAscending, Header:=xlYes
For i = Cells(Rows.Count, "b").End(xlUp).Row To 3 Step -1
If Cells(i, 3) < 70 Or Cells(i, 4) < 70 Or Cells(i, 5) < 70 Or _
Cells(i, 6) = "D" Or Cells(i, 7) = "D" Or Cells(i, 8) = "D" Or _
Cells(i, 9) < 100 Or Cells(i, 2) = Cells(i - 1, 2) _
Then Rows(i).Delete
Next i
End Sub
--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
Hello,

I have a macro that does a lot of things, but I can't get it to work due
to
syntax errors. This wa sa cut and past from a newsgroup.
any help?

Here was original request of what I was trying to do.

I would like to know how I can write a macro that will do the following:

1. I have 3 columns in the spreadsheet that have numbers that range from
1
to 99. If any one of the 3 numbers in the row are less than 70, then
delete
the row.

2. I have 3 columns in the spreadsheet that have letters that range from
A
to E. If any one of the 3 letters equal D or E then I want to delete the
complete row.

3. How can I sort the data once it goes through the above macros to remove
any duplicate data based on the second column? ie: if it is a repeat
symbol
like AAPL delete the row. I do have a header row on row 1.

4. How can I sort based on a number in that column? If the number is less
than 100, then delete the row.

Thanks
--
Mike B

Here is the code


Sub DelRows()
Dim R&, C&, E&
C = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'Sort on Second Column
'Question #3 Part 1
Cells(1, 1).Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Loop Through Each Row
For R = 2 To C
'Prevent endless loop
E = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
If R E Then Exit For
'Delete Rows where Columns 3, 4, & 5 are < 70
'Question #1
If Cells(R, 3) < 70 Or Cells(R, 4) < 70 Or Cells(R, 5) < 70 Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Delete Rows where Columns 6, 7, & 8 are = "D"
'Question #2
If Cells(R, 6) = "D" Or Cells(R, 7) = "D" Or Cells(R, 8) = "D" Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Delete Rows where Columns 9 is less than 100
'Question #4
If Cells(R, 9) < 100 Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
'Remove Dublicates in Column 2
'Question #3 Part 2
If Cells(R, 2) = Cells(R - 1, 2) Then
Rows(R).EntireRow.Delete
R = R - 1
Next R
End If
Next R
Cells(1, 1).Select
End Sub

--
Mike B