CODEWITHSUNDEEP
excelvba
curtisp
curtisp

Reputation: 2315

Excel VBA sort not working what is correct syntax for worksheet.range reference

With the code below I get an error:

Run-time error '1004' Method 'Range' of object'_Worksheet failed.

   Dim destLastCol As Integer 'last column in range
   Dim destLastRow As Integer  'last row in range 
   Dim wsCrewDetail As Worksheet '

   Set wsCrewDetail = Worksheets("CrewDetail_M")
   destLastCol =  integer assigned previously
   destLastRow =  integer assigned previously

   With wsCrewDetail.Range(Cells(4, 1), Cells(destLastRow, destLastCol)) <== error here
    .Sort Key1:=.Cells(4, 2), Order1:=xlAscending, _
     key2:=.Cells(4, 1), Order2:=xlAscending, _
     key3:=.Cells(4, 3), order3:=xlAscending, Header:=xlYes
   End With

I've searched and viewed many examples trying many variations on setting the Range reference and nothing is working.

What is correct reference syntax please?

Edited to add destLastRow = integer assigned previously and edit to show destLastCol

Upvotes: 1

Views: 3699

Answers (1)

brettdj
brettdj

Reputation: 55682

Well you aren't feeding a row number into destLastRow in this line
With wsCrewDetail.Range(Cells(4, 1), Cells(destLastRow, destLastCol))

Plus, don't use Integer variables, use Long. They are more efficient and also cater for large numbers.

suggestions

  • using shorter variables such as ws for Worksheet makes it easier to type and read code
  • use Long rather than Integer
  • I would normally set a working range (rng1) rather than use a longer object starting with the sheet
  • dummy destLastRow and destLastCol values

sample code

   Dim destLastCol As Long 'last column in range
   Dim destLastRow As Long  'last row in range
   Dim ws As Worksheet
   Dim rng1 As Range

   Set ws = Worksheets("CrewDetail_M")
   destLastCol = 6
   destLastRow = 10

   Set rng1 = ws.Range(ws.Cells(4, 1), ws.Cells(destLastRow, destLastCol))
   With rng1
    .Sort Key1:=.Cells(4, 2), Order1:=xlAscending, _
     key2:=.Cells(4, 1), Order2:=xlAscending, _
     key3:=.Cells(4, 3), order3:=xlAscending, Header:=xlYes
   End With

Upvotes: 4

Related Questions