Sort Cell Columns VBA

[ststern45]

Hello,

I am having trouble sorting cell columns B:D
Column B and D are the only columns with data
Column B is the column I am sorting in ascending order with column D

This is the code I have so far: Thanks in advance

Sub SortColumns()

    Dim aRng As String
    Dim bRng As String
  
    Application.ScreenUpdating = False

'aRng = cell column B
'bRng = cell column D

'G1 = starting row cell always 1
'G2 = this row chnages based on the new number of entries
  
    aRng = Sheets("History1").Range("F1").Value
    bRng = Sheets("History1").Range("F2").Value


Columns("B:D").Select
    ActiveWorkbook.Worksheets("History1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("History1").Sort.SortFields.Add Key:= _
        Range(aRng & Range("G1").Value & ":" & bRng & Range("G2").Value), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("History1").Sort
        .SetRange Range(aRng & Range("G1").Value & ":" & bRng & Range("G2").Value)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Application.ScreenUpdating = True
  
End Sub

This is the code using macro recorder:

Columns("B:D").Select
    ActiveWorkbook.Worksheets("History1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("History1").Sort.SortFields.Add Key:= _
        Range("B1:B10579"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("History1").Sort
        .SetRange Range("B1:D10579")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[HaHoBe]

Hi ststern45,

what about

Sub SortColumns_mod()
' https://www.mrexcel.com/board/threads/sort-cell-columns-vba.1227161/
Dim lngLRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ActiveWorkbook.Worksheets("History1")

lngLRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
With ws.Sort
  .SortFields.Clear
  .SortFields.Add Key:=ws.Range("B1:B" & lngLRow), _
                  SortOn:=xlSortOnValues, _
                  Order:=xlAscending, _
                  DataOption:=xlSortNormal
  .SetRange ws.Range("B1:D" & lngLRow)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
Set ws = Nothing

Application.ScreenUpdating = True

End Sub

Ciao,
Holger

 

[ststern45]

Thank you the reply and the code. I will try this and will definitely reply.
Thanks,
Steve

 

[ststern45]

Thank you again for the code.
Everything works great except the code skips over cell B1:D1
Everything sorts perfectly with the exception of these 2 cells.
Thanks
Steve

 

[HaHoBe]

Hi Steve,

the code has the codeline

  .Header = xlYes

which excludes the first row of the area from sorting, If you know that there will be no headers to stay on top of the data you may either use

  .Header = xlGuess

where Excel will decide if Row 1 will be included in the sorting or use

  .Header = xlNo

to advise to sort the complete area no matter if there are headers or not.

Holger

 

[Peter_SSs]

@ststern45
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[ststern45]

Hi Steve,

the code has the codeline

  .Header = xlYes

which excludes the first row of the area from sorting, If you know that there will be no headers to stay on top of the data you may either use

  .Header = xlGuess

where Excel will decide if Row 1 will be included in the sorting or use

  .Header = xlNo

to advise to sort the complete area no matter if there are headers or not.

Holger

Thank you for all your help.
Steve

 

[ststern45]

Hi Steve,

the code has the codeline

  .Header = xlYes

which excludes the first row of the area from sorting, If you know that there will be no headers to stay on top of the data you may either use

  .Header = xlGuess

where Excel will decide if Row 1 will be included in the sorting or use

  .Header = xlNo

to advise to sort the complete area no matter if there are headers or not.

Holger

Thank you again for all your help.
.Header = xlGuess works perfectly

Thank you,
Steve