Selecting and sorting variable range

Skip Bisconer

Active Member
Joined
Jun 14, 2002
Messages
263
It's been quite a while since I have created VBA and now using the latest Excel in Office 365 I am stumbling over something that should be easy. I recorded the following code using record macro but doesn't come out the way I want it. I download a .cvs from my investment account and need to reformat and and arrange before I copy it over to my working data sheets. I have everything working except selecting the entire range of data and sorting in ascending order. The recorded macro gives me the fixed range of data and I need it to be variable as each download .cvs has more or less data. The following is what I get with the recorded macro.
Code:
Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Add Key:= _
        Range("A4:A14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("AccountHistoryReport").Sort
        .SetRange Range("A4:V14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With   
End Sub
I need the range to be A4: to end of data. It's the end of data that I haven't been able to locate.

Thanks for assistance you can give me.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Maybe:
Code:
Sub Test()
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Add Key:= _
        Range("A4:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("AccountHistoryReport").Sort
        .SetRange Range("A4:V" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0
Add this code to the top:

Code:
Dim RowCount as Long
RowCount = RowCount = Sheets(TabName).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

Then replace the body o

Rich (BB code):
Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AccountHistoryReport").Sort.SortFields.Add Key:= _
        Range("A4:A" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("AccountHistoryReport").Sort
        .SetRange Range("A4:V" & RowCount)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With   
End Sub

Let me know if this fixes your problem. I haven't fully read through the code, just edited what I thought you would want since I'm a bit rushed for time. If there are any errors let me know and I'll try troubleshooting it when I get a second.
 
Upvote 0
Mumpa:
This doesn't sort the data. When I step through it it goes pretty fast as I only have a few lines of data but it does't seem like it doesn't actually select the data. Would you please take another look at it.

Thanks for your help.
 
Last edited:
Upvote 0
It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
mumps
Here is the link
https://www.dropbox.com/s/oy9oqoxjl2foh45/AccountHistoryReport.csv?dl=0
The macro I am trying to create
I am trying to format this .cvs to update my working spreadsheet. 1. Starting with A3 TansactionID I want to delete the entire column including A3. 2.move all the other data starting including the Date header to column A.
3. Format the Date column to d-mmm-mm

4. Select and sort the entire database by the Date column A starting with A4 in ascending order.
This file will have over it use can have from 1 to variable rows of data.

Step 4 is where my macro dies.

Thanks for any help you can give me. A lot vba verbiage has changed since I last worked in it.
 
Upvote 0
Try:
Code:
Sub SortRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:A2").Cut Range("B1")
    Columns(1).EntireColumn.Delete
    Range("A3:A" & LastRow).NumberFormat = "d-mmm-yy"
    ActiveWorkbook.Worksheets("Skip Bisconer").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Skip Bisconer").Sort.SortFields.Add Key:=Range( _
        "A1:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Skip Bisconer").Sort
        .SetRange Range("A3:U" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub
After pasting the macro into a regular module, you will have to re-save it as a macro-enabled file.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,216,515
Messages
6,131,110
Members
449,620
Latest member
MatXm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top