Combine and sort 2 columns

[theta]

Hi all...I haven't used VBA for over a year so I am very rusty. I thought of a few ways of doing this, but nothing elegant presented itself.

I have 'times' (00:00hhmm) stored in a range (N7:O106). There is a preceeding column (M7:M106) that contains a '1' or a '0' (is a patrol required, true or false).

I am trying to form a macro that runs through each cell in M7:M106 and for each cell that contains a '1', it will grab the adjacent column N and column column O values (if not blank) and add them to an array.

E.g. C19 = 1, therefore add N19 to the array (if not blank) and add O19 to the array (if not blank).

The array should then be sorted by ascending and dumped into 'Sheet2' D5, with no blanks.

I have named the ranges Flight_Details_Patrol_Required (M7:M106) and Flight_Details_Flight_Times (N7:O106). This range is exhaustive, it would be better to define the start e.g. M7 and then let excel define the last row that contains data, based on the values contained within column M.

Any help greatly appreciated...

[SamT]

Place this code in the module of the sheet with columns M to O


Sub SamT()
'For Help, see: "http://www.vbaexpress.com/forum/showthread.php?53544-Combine-and-sort-2-columns"
Dim Cel As Range
Dim rw As Long
Dim sheet2 As Worksheet

Set sheet2 = Sheets("Sheet2")
rw = 5

For Each Cel In Range(Range("M7"), Range("M" & Rows.Count).End(xlUp))
If Cel = 1 And _
Cel.Offset(0, 1).Value <> "" And _
Cel.Offset(0, 2).Value <> "" Then
Cel.Offset(0, 1).Resize(0, 2).Copy sheet2.Range("D" & rw)
rw = rw + 1
End If
Next Cel

sheet2.Range(Range("D5"), Range("E" & Rows.Count).End(xlUp)).Sort _
Key1:=Range("D5"), Order1:=xlAscending, _
Key2:=Range("E5"), Order2:=xlAscending, _
Header:=xlGuess
'Key2 line in case two flights departing same time, returning different times
End Sub

[theta]

Place this code in the module of the sheet with columns M to O


Sub SamT()
'For Help, see: "http://www.vbaexpress.com/forum/showthread.php?53544-Combine-and-sort-2-columns"
Dim Cel As Range
Dim rw As Long
Dim sheet2 As Worksheet

Set sheet2 = Sheets("Sheet2")
rw = 5

For Each Cel In Range(Range("M7"), Range("M" & Rows.Count).End(xlUp))
If Cel = 1 And _
Cel.Offset(0, 1).Value <> "" And _
Cel.Offset(0, 2).Value <> "" Then
Cel.Offset(0, 1).Resize(0, 2).Copy sheet2.Range("D" & rw)
rw = rw + 1
End If
Next Cel

sheet2.Range(Range("D5"), Range("E" & Rows.Count).End(xlUp)).Sort _
Key1:=Range("D5"), Order1:=xlAscending, _
Key2:=Range("E5"), Order2:=xlAscending, _
Header:=xlGuess
'Key2 line in case two flights departing same time, returning different times
End Sub


Perfect, I will give it a try now and report back!

Thanks for the quick reply...