Problem sorting a range to single column

54edser

New Member
Joined
Sep 3, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi and welcome to the forum.

This should be an improvement:
Code:
Sub SortRange()
     Dim rRange As Range
     Dim lCol As Long
    
     Set rRange = Range("A2:H10")
    
     With rRange
        For lCol = 2 To .Columns.Count
            With .Columns(lCol)
                .Copy
                Cells(Rows.Count, "A").End(xlUp)(2, 1).PasteSpecial xlValues
                Application.CutCopyMode = False
            End With
        Next lCol
     End With

 End Sub

Start the copy from column B (ICol=2).
Find the last empty cell starting from the bottom.
Code:
   Cells(Rows.Count, "A").End(xlUp)(2, 1).PasteSpecial xlValues

I hope this helps,
 

Watch MrExcel Video

Forum statistics

Threads
1,108,788
Messages
5,524,879
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top