Selecting and sorting variable range

[Skip Bisconer]

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.

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.

 

[mumps]

Maybe:

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]

Add this code to the top:

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

Then replace the body o

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]

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.

 

[mumps]

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]

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]

Try:

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.