Sorting an unknown Range

Question

I have a situation where I will be importing multiple worksheets from a mainframe computer where the mainframe programmer spaced out the data into every other row for readability online. However, in Excel it would be best to eliminate the blank rows.

From what I can see, the best method is to select the range and sort it after defining my header rows.

That works great until I want to automate via VBA.

This is what I attempted which does not work :(

Sub GetRidBlankRow()

Dim r As Range

Dim LastRow As Long

Dim x As String

x = "July" 'Sheet Name - In my final I will use a message box to get the proper sheet name

    Set r = Range("a1")

    LastRow = Cells(Rows.count, "a").End(xlUp).row

Range("A1").Select

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    ActiveWorkbook.Worksheets(x).Sort.SortFields.Clear

    ActiveWorkbook.Worksheets(x).Sort.SortFields.Add Key:=Range(r, r.End(xlDown)), _

        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets(x).Sort

        .SetRange Range(r, r.End(xlDown)).Resize(, 4)

        .HEADER = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

Answer 1

Hi,

Maybe a slightly simpler sort routine. This assumes the sheet to sort is the active one. I've used resize(,4) like your code because I assume it's the rows that are unknown and not the columns. If you don't know the columns we can fix that.

Sub Sortem()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "a").End(xlUp).Row

Range("A1:A" & LastRow).Resize(, 4).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _

        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

        DataOption1:=xlSortNormal

End Sub

Answer 2

Thanks Mike -

I have been having a hard time wrapping my head around this. Your solution works perfectly.

Dawn