Sort Columns with dynamic range

[cajsoft]

Hi Experts,
I have an excel sheet where the data "in Plan" columns are all copied in with a macro from different sheets. I need a way programmatically (ie as part of the same macro) to Sort the columns by Row number 2 - "Ship Date" (left to right).

I want the sort to happend At the end of the current VBA code in the macro. I have saved the starting Column and End Column numbers as variables.. ie in this case "E" and "N".

I've tried to use similar code to this -

Range("E1:N1").Sort Key1:=Range("E2:N2"), Order1:=xlAscending, Orientation:=xlLeftToRight

but it keeps failing (obviously I'd need to replace the static column values with a variable, but in this case I was just testing)

please help.

 

[DanteAmor]

Try this:

Sub sortcolumns()
  Dim lr As Long
  Dim sh As Worksheet
  
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("E:N").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  sh.Range("E1:N" & lr).Sort Key1:=Range("E2:N2"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight, Header:=xlNo
End Sub

 

[cajsoft]

Try this:

Sub sortcolumns()
  Dim lr As Long
  Dim sh As Worksheet
 
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  lr = sh.Range("E:N").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  sh.Range("E1:N" & lr).Sort Key1:=Range("E2:N2"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight, Header:=xlNo
End Sub

thanks, that works a treat.. I tried to adjust the code to allow variables in the Range but it's not working.. here's a test I did using a String.

not sure why its not working as I thought it was just a string value in the Range.

    strRange = "E:N"
    strKeyRange = "E2:N2"
    Set sh = ActiveSheet
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range(strRange).Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    sh.Range(strRange & lr).Sort Key1:=Range(strKeyRange), Order1:=xlAscending, Orientation:=xlLeftToRight, Header:=xlNo

 

[DanteAmor]

strRange = "E:N"

Try this:

strRange = "E1:N"

 

[cajsoft]

Try this:

strRange = "E1:N"

thank you.. I figured it out that I had a typo after I posted this..

had to make a dirty looking string to hold all the values.. no ideal.. but it works

strSortStartRange = Split(Cells(1, intStartColumn).Address(True, False), "$")
strSortEndRange = Split(Cells(1, intStartColumn).Address(True, False), "$")

strRange = strSortStartRange(0) & ":" & strSortEndRange(0)
    strRange2 = strSortStartRange(0) & strSortStartRange(1) & ":" & strSortEndRange(0)
    strKeyRange = strSortStartRange(0) & "2:" & strSortEndRange(0) & "2"