G'day.
A while ago JBeaucaire provided me with code for a macro that copied data from one sheet, pasted it into another sheet, deleted empty and duplicate cells and sorted the results. I tweaked the code to get it to work in my work book and ended up with the following code:
Option Explicit
Sub MakeList()
Dim LR As Long, LC As Long, i As Long
LR = Range("AA4").SpecialCells(xlCellTypeLastCell).Row
LC = Range("AA4").SpecialCells(xlCellTypeLastCell).Column
Application.ScreenUpdating = False
'Copy all data to second sheet in one column
For i = 26 To LC
Range(Cells(2, i), Cells(LR, i)).Copy _
Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
Next i
'Clear out null cells and sort
Sheets("Sheet1").Activate
Range("A1") = "List"
Range("B1") = "Key"
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B2:B" & LR).FormulaR1C1 = "=OR(RC1="" "",RC1="""")"
Range("A2").AutoFilter Field:=2, Criteria1:="TRUE"
Range("A2:B" & LR).SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
'Cleanup
Rows("1:1").Delete
Columns("B:B").ClearContents
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=xlNo
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Can anyone tell me how to make the code paste the first result into cell A2 so I can have a title in cell A1. I'm sure it is very easy, I just don't know how to do it.
Cheers
Bookmarks