Stop sort after data is sorted with button

[Gryder]

I have basic sort code that sorts my information correctly and it uses a button. But if I press the button again (a second time), it moves my data up if there are blank row/rows and I don't want it to fill in the blank rows. I would like to have a msgbox that says my data is already sorted.

In my code its looking at Column L. In the cell I put 1, 2, 3, 4, and 5 numbers. They represent Week, Month, Quarterly, SemiAnnual and Annual (1=week, 2=Month etc.) these numbers are static with the row. I believe I need an If else statement but when I searched, I didn't find much information on it.

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set wsData = ThisWorkbook.Worksheets("ConMon Due")

Set rngData = wsData.Range("A4:L4" & lastrow)

rngData.Sort key1:=Range("L4"), order1:=xlAscending, Header:=xlYes

Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

Let's change the way we find the last row. In your current version you are getting the last row of column A, but you are sorting by column L, so column A does not necessarily have the same number of rows as column L. To play it safe, let's find the last row of the column range from A to L.

And another important thing, in this line: Set rngData = wsData.Range("A4:L4" & lastrow) , you are concatenating:
A4:L4 & the value of the last row
assuming that the last row is 10. So the range instead of being: A4:L10, you are putting it as:
A4:L4 & 10
so:
A4:L410

Try:

Private Sub CommandButton2_Click()
  Dim lastrow As Long
  Dim wsData As Worksheet
  Dim rngData As Range
 
  Application.ScreenUpdating = False
 
  lastrow = Range("A:L").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  Set wsData = ThisWorkbook.Worksheets("ConMon Due")
  Set rngData = wsData.Range("A4:L" & lastrow)
  rngData.Sort key1:=Range("L4"), order1:=xlAscending, Header:=xlYes
 
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

This sounds quite like it might be related to the same worksheet/data as this previous thread.
If that is so, have you changed to having 4 header rows instead of 3?
Also, if it is related, maybe we can use a similar approach to the one used there?
In my code below I have assumed still 3 header rows but marked in the code a change if 4.
I have also assumed the button for the code is on the relevant sheet that the data is on & that column A is not formulas.

Private Sub CommandButton2_Click()
  With Range("A4:L" & Cells(Rows.Count, "A").End(xlUp).Row)
    If .Columns(1).SpecialCells(xlConstants).Count = .Rows.Count Then
      Application.ScreenUpdating = False
      .Sort Key1:=.Columns(12), Order1:=xlAscending, Header:=xlNo 'Use xlYes if now 4 header rows
      Application.ScreenUpdating = True
    Else
      MsgBox "Blank rows have already been added"
    End If
  End With
End Sub

One further recommendation in relation to vba code:
I suggest that you force the declaration of all variables in your code by, in the vba window
Tools -> Options -> Editor tab -> Required Variable Declaration

Over time it will save you a lot of time searching for errors.