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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,121
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
 

MattH1

Board Regular
Joined
Jul 15, 2016
Messages
174
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.
 

Skip Bisconer

Active Member
Joined
Jun 14, 2002
Messages
263
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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,121

ADVERTISEMENT

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.
 

Skip Bisconer

Active Member
Joined
Jun 14, 2002
Messages
263
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,121
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,733
Members
414,170
Latest member
Mdm

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
Top