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.

 

[Gryder]

Hey Peter, I made the change to add the variable declaration. Thanks for that tip. And yes, the code is on the same sheet as the Insert blank row button that you helped me with before. The header has not changed, I'm still using rows 1-3 for the header.

So, I tried the code, and I thought it worked perfect but when data is left over and not transferred back to their corresponding sheet (Week, Month, etc) and new data is inserted like SemiAnnual, the data moves to the next available row, seen in the picture below. (Which is normal)

In column K, it shows Week, Quart, Annual and SemiAn. There is also a blank row inserted in-between Quart and Annual.

Here's where the problem is. Now I should be able to hit the Sort button and SemiAnnual should move above Annual but that does not happen and I get the message box saying "Blank rows have already been added". It's the same thing for trying to insert Blank Rows. I get the message block saying the same thing, because there is already a blank row between quart and annual. The button should see that there are not blank rows between Week and Quart and insert a blank row.

I should be able to hit the Insert a Blank Row button or Sort button from the picture below to add a row or sort the data but again, I get the message "Blank rows have already been added".

Garrett

 

[Peter_SSs]

I'm still using rows 1-3 for the header.

Thanks for clearing that up.

when data is left over and not transferred back to their corresponding sheet

I didn't understand what is meant by "data is left over" or "not transferred back ..." however see if this is any use. (It can be run from a button-click)

This code ..

• Removes any rows that are blank in column K
• Sorts the data based on column L
• Inserts blanks (again) between the different categories.
• (Hopefully) formats everything as you want.

Sub Remove_Sort_Insert()
  Dim rws As Long, r As Long
  
  Application.ScreenUpdating = False
  With Range("A3:L" & Cells(Rows.Count, "K").End(xlUp).Row)
    rws = .Columns(11).SpecialCells(xlConstants).Count - 1
    .Sort Key1:=.Columns(12), Order1:=xlAscending, Header:=xlYes
    .Offset(rws + 1).Clear
    .Rows(2).Copy
    .Offset(1).Resize(rws).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    For r = rws To 2 Step -1
      If .Cells(r, 11).Value <> .Cells(r + 1, 11).Value Then
        .Rows(r + 1).Insert Shift:=xlDown
        .Rows(r + 1).Clear
      End If
    Next r
    .Cells(1, 1).Select
  End With
  Application.ScreenUpdating = True
End Sub

 

[Gryder]

When I said Data is left over, we go over task Weekly, monthly, quarterly, etc. If a task is not complete, the row of data stays in the sheet I took a screen shot of. If we complete a task, it gets moved to the Weekly, monthly, quarterly etc. sheet.

I tried the code and it worked really well at first but then I noticed when data is moved to the next available row and I select the new button "Remove_Sort_Insert" it deletes the last row of data. So if I have rows 3-10 of data, it will delete row 10 of data and if you select the Button again, it starts deleting the last row in the sheet. Can we put the MsgBox back in? Stating all data is sorted and blank lines are inputted.

I did try to place the MsgBox in the code and I got the Else without the If error. I realized the code does not have the If statement above to make that work.

Other than the row getting deleted the first time you hit the button and therefore after, the code worked as expected. It sorted the data and inserted a blank row between the Week, Month etc.

 

[Peter_SSs]

when data is moved to the next available row

Can you use XL2BB to post a small set of sample data (any sensitive data disguised) with all columns A:L after my code has been run for the first time but before "data is moved to the next available row".
And then post again with XL2BB the same sheet after "data is moved to the next available row" but before my code is run for a second time.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)