VBA/Macro to sort rows by first date in multipe columns?

[Verona1108]

I am putting together a spreadsheet to help a friend at work. She will have five columns of potential dates. Sometimes there may be only one date in a row or there could be 3 or 4. I need a macro that will automatically sort the rows, based on the first date in one of the five columns. If the first column is blank, it would look in the second column, etc to sort by the first available date.

Sample of data unsorted

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Description
[/TD]
[TD]Date 1
[/TD]
[TD]Date 2
[/TD]
[TD]Date 3
[/TD]
[TD]Date 4
[/TD]
[TD]Date 5
[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Sample 1[/TD]
[TD]4/15/13[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/5/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Sample 2[/TD]
[TD][/TD]
[TD]5/1/13[/TD]
[TD]5/15/13[/TD]
[TD][/TD]
[TD]7/20/13[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Sample 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/7/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Sample 4[/TD]
[TD][/TD]
[TD]2/15/13[/TD]
[TD][/TD]
[TD]4/12/13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sample of data sorted

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Description
[/TD]
[TD]Date 1
[/TD]
[TD]Date 2
[/TD]
[TD]Date 3
[/TD]
[TD]Date 4
[/TD]
[TD]Date 5
[/TD]
[/TR]
[TR]
[TD]Scott[/TD]
[TD]Sample 4[/TD]
[TD][/TD]
[TD]2/15/13[/TD]
[TD][/TD]
[TD]4/12/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Sample 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/7/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Sample 1[/TD]
[TD]4/15/13[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/5/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Sample 2[/TD]
[TD][/TD]
[TD]5/1/13[/TD]
[TD]5/15/13[/TD]
[TD][/TD]
[TD]7/20/13[/TD]
[/TR]
</tbody>[/TABLE]

I have very basic experience with Macros and VBA. I know how to add them and make simple edits as long as I know what the references are for. Any help would be greatly appreciated. I have been searching forever for a sample similar to this and have not had any luck. Maybe I am using the wrong terms.

Thanks in advance for any help!

 

[JLGWhiz]

Give this a try:

Code:
Sub firstdate()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Columns("c").Insert
For i = 2 To lr
With sh
If .Cells(i, 4) <> "" Then
.Cells(i, 3) = .Cells(i, 4).Value
Else
.Cells(i, 3) = .Cells(i, 3).End(xlToRight).Value
End If
End With
Next
sh.Range("A1").CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
Columns("C").Delete
End Sub
Code:

 

[meldoc]

Hello and welcome,

I may have missed the point and grossly oversimplified this, but the code below seems to do what you ask.
Please try it on a sample of your data.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> testdate()<br>  lr = Cells(Rows.Count, "A").End(xlUp).Row<br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets(1)<br>      .Range("H2:H" & lr).Formula = "=Min(B2:G2)"<br>      .Range("A2:H" & lr).Sort key1:=Range("H2"), _<br>        Orientation:=xlTopToBottom, Header:=xlYes<br>      .Range("H2:H" & lr).Clear<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>   Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>