Auto sort row based on date at save

March 13, 2019 at 09:04:15
Specs: Windows 10
I have a list in Excel for new user profiles. Each row is a different user and their data is populated in columns A through N. Column A is their start date. I would like to automatically sort these users based off of their start date each time I save the document. Having a window pop-up that states "Saved and Sorted" or something along those lines is fine. I have played around with macros for this and I can get the document to sort column A by date, but I have two problems that I need help with.

Problem 1: Column A is only sorting. So the date is sorting correctly but it does not include the other columns. As such, the start date for each user is incorrectly being shown. I need the sorting to maintain each row. Link every column within that row to sort.

Problem 2: Some users are input without a listed start date for various reasons. Independent contracts for example. In these cases, the start date is left blank. This is automatically sorting at the end of the date range, within the last rows. How can I make this sort in the first few rows instead?

Any help in creating a macro would be greatly appreciated.
Thank you.


See More: Auto sort row based on date at save

Reply ↓  Report •

#1
March 13, 2019 at 11:56:21
If you have already created a macro that gets you close, why not post the code so that we can see what it does? It may just need some minor tweaking.

Please click on the following How-To link and read the instructions on how to post VBA code in this forum.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
March 13, 2019 at 12:24:34
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r As Long

If Not Intersect(Target, Range("A1")) Is Nothing Then
r = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A500" & r).Sort Key1:=Range("A1:A500"), Order1:=xlAscending, Header:=xlYes
End If
End Sub

message edited by Slippers


Reply ↓  Report •

#3
March 13, 2019 at 16:40:30
Let's start with this:

r = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A500" & r).Sort

Are you aware that if r = 15, you are telling the code to sort A1:A50015?

Is that what you want?

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
March 14, 2019 at 06:18:36
I'll likely have about 300-400 items on this list. I used 500 so that I have plenty of wiggle room in the event I add well over 400. I do not need it to sort 50015 lines.

Reply ↓  Report •

#5
March 14, 2019 at 13:32:36
You don't need the "500"

If "r" is the last row of data that you want to Sort, then use this:

r = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & r).Sort

When you concatenate the value of r onto A500, the 500 doesn't disappear, VBA simply tacks the value of r onto the end of the string. In other words if r = 375, your instruction would reference A1:A500375. Eliminate the 500 and the code wll reference A1:A375.

In addition, let the code determine the Sort Range and the Sort Key using r. You'll never have to worry about how many names you add. Well, at least up to 1,048,576 names.

As far as sorting all of the data, you need to tell VBA about the entire range, A1:Nr. Try this:

r = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:N" & r).Sort Key1:=Range("A1:A" & r), Order1:=xlAscending, Header:=xlYes

As far as the blank "dates" ending up at the top, the following might work. The code will fill the blank cells with a -1, which is a lower number than any actual Date that you have in Column A. With an Ascending sort, all of the -1's will end up at the top. Once the sorting is done, the -1's are "deleted".


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r As Long

  If Not Intersect(Target, Range("A1")) Is Nothing Then

'Determine last Row with data
     r = Cells(Rows.Count, "A").End(xlUp).Row

'Fill Blank cells with -1
       Range("A1:A" & r).Replace What:="", Replacement:="-1"

'Sort
           Range("A1:N" & r).Sort Key1:=Range("A1:A" & r), Order1:=xlAscending, Header:=xlYes

'Delete -1's
       Range("A1:A" & r).Replace What:="-1", Replacement:=""

  End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
March 15, 2019 at 11:35:50
The code you provided at the bottom of the last post seemingly does nothing. I pasted the code but nothing happens on the sheet. I'm actually confused as to why this is and I assume I have a setting wrong. Is there a reason why a sheet would stop or not react to a code?

Reply ↓  Report •

#7
March 15, 2019 at 12:54:03
I'm going to guess that you have just proven the point I was trying to make in Response #5 of your other thread.

I'll bet that the missing parenthesis that you mentioned caused the code to stop after the Application.EnableEvents = False instruction was executed. As noted in the "suggestion" portion of that post, Events are now disabled and no event code will run.

If that is indeed the case, there are a few ways to fix it.

1 - Since Application.EnableEvents is an Application level instruction, Events are disabled throughout that particular instance of Excel. Close all workbooks, quit Excel and restart it. Events will be enabled at startup.

2 - Open the Immediate window in the VBA editor and execute a single instruction:

IOW, type Application.EnableEvents = True in the Immediate window and press Enter. Events will be enabled.

3 - Write and run a real short macro. Events will be enabled.

Sub TurnEventsOn()
 Application.EnableEvents = True
End Sub

Now you see why I said that you should only disable events when you really need to. It limits the chance that a error that is unrelated to an event will cause events to remain disabled when the code crashes.

If that doesn't work, come on back and we'll see what else we can find. A direct Copy/Paste of that code into a sheet module worked for me, so I'm guessing that events are disabled on your system.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
March 15, 2019 at 13:34:39
I tried all of those suggestions, as well as copying some information into a new sheet and the code still does nothing.

I made a separate thread about my other question because it involves a different formula, but this is actually being done on the same spreadsheet. I want to have the system automatically sort by date within the "Completed" sheet after I move a row in with the drop down function. That sheet will near 400 rows. The goal is to create an excel template to use each year. We have close to 400 new employees each year with turnover.

Would you like to continue separate conversation about different codes in different threads, or should we close one and continue to discuss in a single thread?


Reply ↓  Report •

#9
March 15, 2019 at 16:59:32
The code that I posted in Response #5 is a Worksheet_SelectionChange macro that will run when A1 is selected.

That's the type of macro you posted in your Response #2 when I asked for the code that you were using.

Why are you using a Worksheet_SelectionChange macro that runs when A1 is selected if you want the code to run when data is pasted into the next empty row via a different macro?

One more thing: Why did you say in your OP that you wanted the data sorted "each time I save the document" when now you are saying you want the data to "automatically sort by date within the "Completed" sheet after I move a row in"?

This isn't an issue of 2 separate threads, it's an issue of requirements that are not clear, not complete and that change mid-stream.

message edited by DerbyDad03


Reply ↓  Report •

#10
March 18, 2019 at 10:29:03
That is the code I was provided with by a coworker that I've been basing this off of. I asked my initial questions to him and he suggested we search for more help online. I would prefer to have this happen automatically, but I am fine with it sorting after a save as originally requested. I apologize for the confusion in that the project has changed to reflect that and I appreciate your continued help.

I'll try to be more direct here with my request and I will align this with my other thread.
1. We already have the drop down mostly working as desired. We still need to add post #8 from the other thread into consideration.
2. Once the drop down takes a row from sheet x to sheet y, have sheet y auto sort the data based off of the date in column A.
3. Allow the drop down macro to function on every sheet, including on sheet y without interfering with the additional code created in this thread.

I've gotten the code to work as you intended with my document. I copied the cells as a selection into an new blank document rather than copying the sheet. I still don't know why it wasn't working before but we're at least back on a working template now.


Reply ↓  Report •

#11
March 19, 2019 at 06:28:45
I will work on this and try to combine the requirements from both threads into a single solution.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question