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
|