Select single column to sort by column number

[music_al]

Hi

Could anyone please tell me why this doesnt work...

I get the error "Compile error: Invalid or unqualified reference"

Sub SortColumns()


Dim CurSortCol As Integer
CurSortCol = 7
Do Until CurSortCol = 295

Sheets("Level3Suppliers").Columns(CurSortCol).Select
ActiveWorkbook.Worksheets("Level3Suppliers").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Level3Suppliers").Sort.SortFields.Add Key:=Range(.Cells(2, CurSortCol), .Cells(3097, CurSortCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Level3Suppliers").Sort
.SetRange Range(.Cells(1, CurSortCol), .Cells(3097, CurSortCol))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
CurSortCol = CurSortCol + 1
Loop

End Sub

 

[shg]

The dot before Cells means "use the reference established by the prior With statement", but there isn't one.

 

[music_al]

Thank you so much.

 

[shg]

You're welcome.

 

[shg]

BTW, this should do the same thing:

Sub SortColumns()
  Dim iCol          As Long

  For iCol = 7 To 295
    With Worksheets("Level3Suppliers").Columns(iCol)
      .Range("A2:A3097").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
    End With
  Next iCol
End Sub