| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Assistance please.
Excel Guri
Good people, I am trying to set up a Excel macro that will look at a time value that has to run in a sequence of three. If there is only one occasion on the particular day the entire row is to be deleted row. A small example follows. Can you assist with the code please. A B C D E F G H DELETE 1 20/3/05 1 4:37 MELBOURNE 2 12 3 DELETE 2 20/3/05 1 4:15 BALLINA 6 2 3 DELETE 3 20/3/05 1 4:15 BALLINA 6 6 5 RETAIN 4 19/3/05 7 4:37 TOOWOOMBA 2 5 4 RETAIN 5 19/3/05 7 4:37 TOOWOOMBA 2 8 8 RETAIN 6 19/3/05 7 4:37 TOOWOOMBA 2 9 0 DELETE 7 19/3/05 7 5:15 TOOWOOMBA 3 2 2 DELETE 8 19/3/05 7 5:15 TOOWOOMBA 3 3 2 DELETE 9 19/3/05 7 1:15 BRISBANE 2 3 2 DELETE 10 19/3/05 7 1:15 BRISBANE 2 8 3 RETAIN 11 19/3/05 7 1:05 GEELONG 2 2 2 RETAIN 12 19/3/05 7 1:05 GEELONG 2 3 8 RETAIN 13 19/3/05 7 1:05 GEELONG 2 5 0 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Assistance please.
In a standard module, paste the following code:
Option Explicit Sub Cleanup() Dim LastRow As Long Dim rwindex As Long Dim times() As Boolean ' create an array to hold the checks Const FORMULA As String = "=SUM((R1C2:RXC2=RC2)*(R1C4:RXC4=RC4))" LastRow = Range("A1").End(xlDown).Row Columns(1).Insert For rwindex = 1 To LastRow With Cells(rwindex, 1) .FormulaArray = Replace(FORMULA, "X", LastRow) End With Next For rwindex = LastRow To 1 Step -1 If Cells(rwindex, 1) < 3 Then Rows(rwindex).Delete End If Next Columns(1).Delete End Sub Method: Uses a formula array to count the rows in the table where two items in each row match. Once we have the count, we remove those rows where the count is less than three. Patrick Molloy Microsoft Excel MVP "Father Alf" wrote: Excel Guri Good people, I am trying to set up a Excel macro that will look at a time value that has to run in a sequence of three. If there is only one occasion on the particular day the entire row is to be deleted row. A small example follows. Can you assist with the code please. A B C D E F G H DELETE 1 20/3/05 1 4:37 MELBOURNE 2 12 3 DELETE 2 20/3/05 1 4:15 BALLINA 6 2 3 DELETE 3 20/3/05 1 4:15 BALLINA 6 6 5 RETAIN 4 19/3/05 7 4:37 TOOWOOMBA 2 5 4 RETAIN 5 19/3/05 7 4:37 TOOWOOMBA 2 8 8 RETAIN 6 19/3/05 7 4:37 TOOWOOMBA 2 9 0 DELETE 7 19/3/05 7 5:15 TOOWOOMBA 3 2 2 DELETE 8 19/3/05 7 5:15 TOOWOOMBA 3 3 2 DELETE 9 19/3/05 7 1:15 BRISBANE 2 3 2 DELETE 10 19/3/05 7 1:15 BRISBANE 2 8 3 RETAIN 11 19/3/05 7 1:05 GEELONG 2 2 2 RETAIN 12 19/3/05 7 1:05 GEELONG 2 3 8 RETAIN 13 19/3/05 7 1:05 GEELONG 2 5 0 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Assistance please.
Thank you Peter
The Macro works perfectly !!! "Patrick Molloy" wrote in message ... In a standard module, paste the following code: Option Explicit Sub Cleanup() Dim LastRow As Long Dim rwindex As Long Dim times() As Boolean ' create an array to hold the checks Const FORMULA As String = "=SUM((R1C2:RXC2=RC2)*(R1C4:RXC4=RC4))" LastRow = Range("A1").End(xlDown).Row Columns(1).Insert For rwindex = 1 To LastRow With Cells(rwindex, 1) .FormulaArray = Replace(FORMULA, "X", LastRow) End With Next For rwindex = LastRow To 1 Step -1 If Cells(rwindex, 1) < 3 Then Rows(rwindex).Delete End If Next Columns(1).Delete End Sub Method: Uses a formula array to count the rows in the table where two items in each row match. Once we have the count, we remove those rows where the count is less than three. Patrick Molloy Microsoft Excel MVP "Father Alf" wrote: Excel Guri Good people, I am trying to set up a Excel macro that will look at a time value that has to run in a sequence of three. If there is only one occasion on the particular day the entire row is to be deleted row. A small example follows. Can you assist with the code please. A B C D E F G H DELETE 1 20/3/05 1 4:37 MELBOURNE 2 12 3 DELETE 2 20/3/05 1 4:15 BALLINA 6 2 3 DELETE 3 20/3/05 1 4:15 BALLINA 6 6 5 RETAIN 4 19/3/05 7 4:37 TOOWOOMBA 2 5 4 RETAIN 5 19/3/05 7 4:37 TOOWOOMBA 2 8 8 RETAIN 6 19/3/05 7 4:37 TOOWOOMBA 2 9 0 DELETE 7 19/3/05 7 5:15 TOOWOOMBA 3 2 2 DELETE 8 19/3/05 7 5:15 TOOWOOMBA 3 3 2 DELETE 9 19/3/05 7 1:15 BRISBANE 2 3 2 DELETE 10 19/3/05 7 1:15 BRISBANE 2 8 3 RETAIN 11 19/3/05 7 1:05 GEELONG 2 2 2 RETAIN 12 19/3/05 7 1:05 GEELONG 2 3 8 RETAIN 13 19/3/05 7 1:05 GEELONG 2 5 0 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel VBA Assistance please.
Peter, Patrick, whatever, its a pleasure ;)
"Father Alf" wrote in message ... Thank you Peter The Macro works perfectly !!! "Patrick Molloy" wrote in message ... In a standard module, paste the following code: Option Explicit Sub Cleanup() Dim LastRow As Long Dim rwindex As Long Dim times() As Boolean ' create an array to hold the checks Const FORMULA As String = "=SUM((R1C2:RXC2=RC2)*(R1C4:RXC4=RC4))" LastRow = Range("A1").End(xlDown).Row Columns(1).Insert For rwindex = 1 To LastRow With Cells(rwindex, 1) .FormulaArray = Replace(FORMULA, "X", LastRow) End With Next For rwindex = LastRow To 1 Step -1 If Cells(rwindex, 1) < 3 Then Rows(rwindex).Delete End If Next Columns(1).Delete End Sub Method: Uses a formula array to count the rows in the table where two items in each row match. Once we have the count, we remove those rows where the count is less than three. Patrick Molloy Microsoft Excel MVP "Father Alf" wrote: Excel Guri Good people, I am trying to set up a Excel macro that will look at a time value that has to run in a sequence of three. If there is only one occasion on the particular day the entire row is to be deleted row. A small example follows. Can you assist with the code please. A B C D E F G H DELETE 1 20/3/05 1 4:37 MELBOURNE 2 12 3 DELETE 2 20/3/05 1 4:15 BALLINA 6 2 3 DELETE 3 20/3/05 1 4:15 BALLINA 6 6 5 RETAIN 4 19/3/05 7 4:37 TOOWOOMBA 2 5 4 RETAIN 5 19/3/05 7 4:37 TOOWOOMBA 2 8 8 RETAIN 6 19/3/05 7 4:37 TOOWOOMBA 2 9 0 DELETE 7 19/3/05 7 5:15 TOOWOOMBA 3 2 2 DELETE 8 19/3/05 7 5:15 TOOWOOMBA 3 3 2 DELETE 9 19/3/05 7 1:15 BRISBANE 2 3 2 DELETE 10 19/3/05 7 1:15 BRISBANE 2 8 3 RETAIN 11 19/3/05 7 1:05 GEELONG 2 2 2 RETAIN 12 19/3/05 7 1:05 GEELONG 2 3 8 RETAIN 13 19/3/05 7 1:05 GEELONG 2 5 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| assistance need with excel doc | New Users to Excel | |||
| Assistance with password in Excel | Excel Worksheet Functions | |||
| How can I get assistance on Excel Formula? | Excel Worksheet Functions | |||
| 1-2-3 @ function to Excel Assistance | Excel Worksheet Functions | |||
| Excel VBA assistance for a noobie | Excel Programming | |||
Linear Mode
