Dawn, change your SetRange to this:
.SetRange Range(r, "A" & LTrim(Str(LastRow)))
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Dawn Bleuel
Sent: Wednesday, January 15, 2014 2:07 PM
To: ExcelVBA
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 (2) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar