Sort with Cells not range-CodePudding

So I'm trying to sort a column in ascending order(the column will change based on a previous integer defined earlier in the code) so I don't want to use Range("B34:B57") etc.

Instead, I've defined the column in question earlier in the code as a variable currentcol, and here's what I have so far:

Dim firstpy As Long
Dim lastpy As Long
            
     firstpy = Cells(34, currentcol)
     lastpy = Cells(57, currentcol)
            
        Range(firstpy & lastpy).Sort Key1:=Cells(34, currentcol), Order1:=xlAscending, Header:=xlNo

I believe the problem is in the Key1 reference? Can someone help sort this out? :)

CodePudding user response：

Sort a One-Column Range

A Quick Fix

Option Explicit

Sub Test()
    
    Const CurrentCol As Long = 1 ' just to make it compile
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim firstPy As Range: Set firstPy = ws.Cells(34, CurrentCol)
    Dim lastPy As Range: Set lastPy = ws.Cells(57, CurrentCol)
    
    Dim rg As Range: Set rg = ws.Range(firstPy, lastPy)
    ' Or without the variables:
    'Set rg = ws.Range(ws.Cells(34, CurrentCol), ws.Cells(57, CurrentCol))
                 
    rg.Sort Key1:=rg, Order1:=xlAscending, Header:=xlNo

End Sub