Hi All, I have a macro that sorts cells in a range (A2:H10) with data (Names) into a single column in A.
<colgroup><col span="8"></colgroup><tbody>
</tbody>
Sub SortRange()
Dim rRange As Range
Dim lCol As Long
Set rRange = Range("A2:H10")
With rRange
For lCol = 1 To .Columns.Count
With .Columns(lCol)
.Copy
Range("A2:H10").End(xlDown)(2, 1).PasteSpecial xlValues
Application.CutCopyMode = False
End With
Next lCol
End With
End Sub
I have problems, firstly when sorting, the macro pastes from the next vacant cell in row A so I end up with the original data in column A duplicated, Secondly if there are any gaps in the data e.g. D2 has no content then none of the cells in Column D will be sorted. Is it possible to modify the macro to a). ignore gaps and sort all the cells in the range, and b). eliminate duplicates in Column A or move sorted range to another Column. Thanks in advance.
<colgroup><col span="8"></colgroup><tbody>
</tbody>
Sub SortRange()
Dim rRange As Range
Dim lCol As Long
Set rRange = Range("A2:H10")
With rRange
For lCol = 1 To .Columns.Count
With .Columns(lCol)
.Copy
Range("A2:H10").End(xlDown)(2, 1).PasteSpecial xlValues
Application.CutCopyMode = False
End With
Next lCol
End With
End Sub
I have problems, firstly when sorting, the macro pastes from the next vacant cell in row A so I end up with the original data in column A duplicated, Secondly if there are any gaps in the data e.g. D2 has no content then none of the cells in Column D will be sorted. Is it possible to modify the macro to a). ignore gaps and sort all the cells in the range, and b). eliminate duplicates in Column A or move sorted range to another Column. Thanks in advance.