sort problem

[CG Rosén]

Hi Group,

Thanks for all help with my previous questions!
Here is another one;

Code below gives an error at the line: With
Sheets(1).............................
Is it not possible to write the range in this format when sorting?

Best Regards

CG Rosén

-------------------------------------------------------------------------------------

a = Application.WorksheetFunction.CountA(Sheets(1).Range("A1:K1")) -2

With Sheets(1).Range(Cells(1, 3), Cells(1, a)) ERROR!!!!!!!!!

.Sort Key1:=Sheets(1).Range("C100"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

End With

 

[Chip Pearson]

If the first worksheet is not the active sheet, your Cells
references will not reference cells on the first sheet; they will
reference cells on the active sheet. Change your With statement
to

With Sheets(1).Range(Sheets(1).Cells(1,3),Sheets(1).Cells(1,a))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[CG Rosén]

Thanks Chip!

If the first worksheet is not the active sheet, your Cells references will
not reference cells on the first sheet; they will reference cells on the
active sheet. Change your With statement to

With Sheets(1).Range(Sheets(1).Cells(1,3),Sheets(1).Cells(1,a))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com