How to use excel macro to sort data with empty cell?

1
votes

Thanks so much for reading my questions. I tried to search the similar questions in the stackoverflow but failed to get the answer...thanks so much if you can help me.

My purpose here is to copy and paste the data from Sheets("Parts") to Sheets("Summary"), and then sort by A column, ignoring the empty cell.

A1: 2.1

A2: 

A3: 1.1

A4: 1.2

After sorting : 

A1: 1.1

A2: 1.2

A3: 2.1

A4:

The macro is success in the Macro builder but then failed in the worksheet (First row empty). Indeed I try to not copying the empty cell with "SkipBlanks" but not functional...

Sub IEMacro()
Dim Lastcell As Range

Sheets("Parts").Range("A3:A300").Copy

With Sheets("Summary")
Set Lastcell = .Range("A65536").End(xlUp)
.Range("A2", Lastcell).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,     SkipBlanks:=True, Transpose:=False
.Range("A2", Lastcell).Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess
End With
2
vbaexcelsorting
Dear all, thanks so much; I sort descending first then ascending, and now it works! It's fantastic!! Thanks so much~especially to R3uK and Linga~How should I close the question? – Joyce Lo
Just click on tick mark below the down arrow of the answer which helps you and up vote it by clicking on the up arrow thats all ;) – Linga

2 Answers

0
votes

This should apply the sorting key properly :

Sub IEMacro()
Dim LastCell As Range

Sheets("Parts").Range("A3:A300").Copy

With Sheets("Summary")
    Set LastCell = .Range("A" & .Rows.count).End(xlUp)
    .Range("A2").PasteSpecial Paste:=xlPasteValues, _
                                        Operation:=xlNone, _
                                        SkipBlanks:=True, _
                                        Transpose:=False
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Sheets("Summary").Range(Sheets("Summary").Range("A1"), LastCell), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
        .SetRange Sheets("Summary").Range(Sheets("Summary").Range("A1"), LastCell)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

End Sub
0
votes

In that case you need to select complete range till the last column of your data then sort it to ascending order, fianlly go with the auto filter.

Sub sort()
'selecting complete range change it as required
    Range("A1:O12").Select
' Replace the sheet name with yours
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
    'sorting complete range change it as required
        .SetRange Range("A1:O11")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub