Re: [ExcelVBA] Sorting an Unknown Range

 

Dear Dawn

This simplified version of your code might work for you :-

Option Explicit

Sub GetRidOfBlankRows()

Dim rRangeToSort As Range

    Set rRangeToSort = Range("a1", Cells.SpecialCells(xlLastCell))

    With ActiveSheet.Sort

        .SortFields.Clear

        .SortFields.Add Key:=Range("a1")

        .SetRange rRangeToSort

        .Header = xlYes

        .Apply

    End With

End Sub

 Regards

Derek Turner

England

+++

---

From: Dawn Bleuel <dlbleuel@gmail.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wednesday, 15 January 2014, 21:06
Subject: [ExcelVBA] Sorting an Unknown Range

 

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"
    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

--

Dawn Bleuel

 

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (4)

Recent Activity:

• New Members 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___