sort data left to right in excel vba

Question

I want to sort complete sheet data by column header alphabetically. Below code works fine but i have to manually enter data range in variables(keyrange and datarange) every time, Since number of columns/rows varies in every file. I tried different ways in below code. Can you advise Is there a way that the last column automatically selected ??like in below W is last column with data in file and code should pick up last column. Similarly last row of columns should pick up into range (like 485 is last row of file in below code), IS it possible ?

 Sub sortfile2()
   Dim keyrange As String
    Dim DataRange As String

    keyrange = "A1:W1"
    DataRange = "A1:W485"

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(keyrange), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(DataRange)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

score 0 · Answer 1 · answered Aug 29 '17 at 09:24

If the source rane is dynamic, you can go with

bottom= Range("A1").End(xlDown).Row
Set DataRange = Range("A1").CurrentRegion.Resize(bottom - 1).Offset(1)

Note that CurrentRegion itself is not enough. you should combine it with Resize and Offset. İf you try with only CurrentRegion and go with F8, you can see why.

score 0 · Answer 2 · answered Aug 29 '17 at 09:29

0

Yes, determining last column and last row is possible.

If you want to determine last column in first (1) row, use the code:

Cells(1, Columns.Count).End(xlToLeft).Column

If you want to get last row in first column, use following:

Cells(Rows.Count, 1).End(xlUp).Row

This is for the first column / row, so you can change it as you want.

answered Aug 29 '17 at 09:29

Michał Turczyn

32,028
14
47
69

thanks. this helped to find last row and column. but i need last column in alphabets as in range have to specify in range in format `Range(A1:W485)` . i added 1 line to get in required format – Srihari Aug 29 '17 at 11:09

score 0 · Answer 3 · answered Aug 29 '17 at 10:59

This is range of data.

Sub test()
    Dim rngDB As Range
    Dim Ws As Worksheet
    Dim r As Long, c As Long

    Set Ws = ActiveSheet

    With Ws
        r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rngDB = .Range("a1", .Cells(r, c))
        rngDB.Select
    End With
End Sub

Or

range("a1").CurrentRegion

score 0 · Accepted Answer · answered Aug 29 '17 at 11:02

yes, Michal answer and other source helped to find exact required output

    'Find the last non-blank cell in row 1
        lCol = Cells(1, Columns.Count).End(xlToLeft).Column
           d = Replace(Cells(1, lCol).Address(True, False), "$1", "")

        'Find the last non-blank cell in column 1
        lRow = Cells(rows.Count, 1).End(xlUp).row
        keyrange = "A1:" & d & 1
        DataRange = "A1:" & d & lRow
'below line is to print (for debugging) the calculated range
   MsgBox (keyrange)
   MsgBox (DataRange)

sort data left to right in excel vba

4 Answers4