Macro skips sort line

[jfish1288]

When I step through the code below, it runs the sort line but does not actually sort the data in excel. It was sorting fine yesterday, now it does not.

The data I am trying to sort is has the following form.

A B C D
1 Date Time Load On/OFF
2 mm/dd/yyyy #### #### On
3 mm/dd/yyyy #### #### Off
4 mm/dd/yyyy #### #### Off

Also this is my first post so I am not sure I am posting this code in the proper way.

Sub SortDelete()
 Dim LastRow As Long
 Dim LastRowPeaks As Long
 Dim x As Long
 
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
 For x = 2 To LastRow Step 1
    Cells(x, 5) = Month(Cells(x, 1)) & Year(Cells(x, 1))
 Next x
    
Worksheets("Sheet3").Range("A1", Range("E1").End(xlDown)).sort _
    Key1:=Range("D2", Cells(LastRow, "D")), _
    Key2:=Range("E2", Cells(LastRow, "E")), _
    Key3:=Range("C2", Cells(LastRow, "C")), _
    order1:=xlAscending, order2:=xlAscending, order3:=xlDescending, _
    header:=xlYes
    
    
 For x = 2 To LastRow Step 1
    If Cells(x, 5) <> Cells(x - 1, 5) Then
    Cells(x, 6) = P
    End If
 Next x
    
 Worksheets("Sheet3").Range("A1", Cells(LastRow, "F")).sort _
    Key1:=Range("F2", Cells(LastRow, "F")), _
    Key2:=Range("A2", Cells(LastRow, "F")), _
    Key3:=Range("D2", Cells(LastRow, "F")), _
    order1:=xlDescending, order2:=xlAscending, order3:=xlDescending, _
    header:=xlYes
    
  LastRowPeaks = Cells(Rows.Count, "F").End(xlUp).Row
  Range(Cells(LastRowPeaks, "A"), Cells(LastRow, "A")).EntireRow.Delete
  
  Range("F2", Cells(LastRowPeaks, "F")).EntireColumn.Delete
  
End Sub

 

[Hermanito]

My guess is you're trying to run this while another sheet than "Sheet3" is active...

You are using a lot of references to ranges and cells without explicitly specifying on which sheet they are... that makes Excel assume you mean the active worksheet at the time the code runs...

You can adapt your code like this (I adapted the first few parts of your code):
put everything between With and End With, like so:

With Worksheets("Sheet3")
   'all code using ranges and cells on Sheet3 should be here
   'using the . operator you can refer to ranges and cells on Sheet3, like so:
   LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
 For x = 2 To LastRow Step 1
    .Cells(x, 5) = Month(.Cells(x, 1)) & Year(.Cells(x, 1))
 Next x
    
Worksheets("Sheet3").Range("A1", .Range("E1").End(xlDown)).sort _
    Key1:=.Range("D2", .Cells(LastRow, "D")), _
    Key2:=.Range("E2", .Cells(LastRow, "E")), _
    Key3:=.Range("C2", .Cells(LastRow, "C")), _
    order1:=xlAscending, order2:=xlAscending, order3:=xlDescending, _
    header:=xlYes
End With

Notice how I've put the dot before all Range and Cell references (and also the one Rows reference you had) that did not have them beforehand... because this is between the With and End With statements, they now all refer explicitly to the ranges and cells on Sheet3, not the active sheet.

 

[jfish1288]

Thanks Hermanito. Your adaptation takes out some possible errors so I will make those changes. I was actually able to get it to run properly again simply by putting a comment line before the sort line. I do not know why that made it work, but I am using excel 2002 on a windows 2000 machine so that might be part of it. Truth be told I probably just had a different sheet active when it wasnt working, and then activated sheet3 and it started working agian.