Making Macros in Excel
 

Hello,

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 lass 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 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

Making Macros in Excel
 

Hi, if I understand what you are trying to do, this would be my cut at
it:


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


HTH--Lonnie M.

Making Macros in Excel
 

Hi Lonnie,

Thanks for the help in coding. How can I put that into a macro in excel. I
tried recording one and then cut and paste the code in, but it came back with
errors. any suggestions on how I can make it into a macro and save it ie:
the steps to do this?

Thanks
Mike b

"Lonnie M." wrote:

Hi, if I understand what you are trying to do, this would be my cut at
it:


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


HTH--Lonnie M.

Making Macros in Excel
 

David McRitchie has some notes at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

mike b wrote:

Hi Lonnie,

Thanks for the help in coding. How can I put that into a macro in excel. I
tried recording one and then cut and paste the code in, but it came back with
errors. any suggestions on how I can make it into a macro and save it ie:
the steps to do this?

Thanks
Mike b

"Lonnie M." wrote:

Hi, if I understand what you are trying to do, this would be my cut at
it:


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


HTH--Lonnie M.



--

Dave Peterson