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

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

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

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