Sort query

[CookieMonster76]

Hi

I have the following which works

ActiveWorkbook.Worksheets("Analysis").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Analysis").Sort.SortFields.Add Key:=Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Analysis").Sort
.SetRange Range("A1:I1404")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The range which it needs to sort is not necessarily always rows 1 to 1404, so I have replaced the red text with

.SetRange Range("A1:I" & Range("A" & Rows.Count).End(xlUp).Row)

but this doesn't work.

Could someone correct me please?

Thanks

Paul

 

[oldbrewer]

maybe use a helper cell that is =counta(A1:A30000)

so in your example that will be 1404 so maybe
.SetRange Range("A1:I & "cells(1,26)"")

assumes helper is Z1

 

[ParamRay]

Hello, this method worked nicely for me:

Public Sub SortWorksheet()
  Dim rngSortRange As Excel.Range
  Dim rngSortField As Excel.Range
  
  On Error GoTo ErrHandler
  With ThisWorkbook.Worksheets("Analysis")
    Set rngSortRange = .Range("A1").CurrentRegion
    Set rngSortField = Intersect(rngSortRange, .Range("H:H"))
    
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=rngSortField, _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending
    
    .Sort.SetRange rngSortRange
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlByRows
    .Sort.Apply
  End With
  
ExitProc:
  Set rngSortRange = Nothing
  Set rngSortField = Nothing
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation, "Sort Worksheet"
  Resume ExitProc
End Sub