How to auto-sort entire rows in excel based on words in one column

[crawl22kp]

Hello, I am not nearly skilled for all this stuff. So here is the issue.

I have my excel tracking sheet with all sorts of names, info, dates, etc. I want to be able to move whole rows up and down depending on the word I change in the first column.

That first column when clicked on has a drop down list of 4 words. I would like to be able to move the first word to a row range between 1-25, second word to cell range between 26-50, third word between cell range 51-75 and fourth word between 75-100.

I don't know if that is possible. I have dates and colors changing as I input data into the row, so need that entire row to go up or down as I change the word in the first column between the four words chosen while maintaining all the functions and stuff I have set up for that row.

I hope that made sense. Any help would be appreciated. I don't know if it would be a simple function or VBA.

Thank you!

 

[James006]

Hello,

With Excel ... everything is possible ...

But your explanation ... so far ... is too vague ...

For example ...

1.Will you always deal with a 100 rows ?

2. Is there only one criteria to sort your rows ...?

etc ...

 

[crawl22kp]

Hello,

With Excel ... everything is possible ...

But your explanation ... so far ... is too vague ...

For example ...

1.Will you always deal with a 100 rows ?

2. Is there only one criteria to sort your rows ...?

etc ...

Yes, the sheet will always just be 100 rows. So that will never change. Short term anyhow.
the criteria to sort the rows will depend on one of four words that is chosen from the drop down list in the cells from the first column.

Lets say the words in the drop down list are "GO", "NO-GO", "MAYBE" and "DISQUALIFIED".

I would like to make the row go into one of the four ranges of cells (1-25, 26-50, etc.) depending on the word chosen for that first cell in the row. That status can and will change, so when it goes from "GO" to "NO-GO" I would like the row to be able to go to the new range of rows accordingly.

Hope that helps. I am not sure how much more in depth I need or can go without all the cool technical terminology I don't know lol. I apologize. Wish I could send the excel sheet to you and talk you through the though process.

 

[James006]

Hello,

You can test following event macro ... which needs to be stored in the Sheet Module ( Alt F11 - Sheet1 for example )

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
' Adjust the range to your situation as well as the Order ''''''''''''''' 
Range("A1:M100").Sort [A1], xlAscending, Header:=xlYes
End Sub

Hope this will help

 

[James006]

Hello,

Once you have tested the macro ... feel free to share your comments ...

 

[crawl22kp]

Thank you for the help. I was busy so it took a minute to get back.
The macro has not worked for me. I was able to put it into the code page. It bring up an error when I change to word in the first column, and the row does not go anywhere.

Compile error:

Ambiguous name detected: Worksheet_change

I know I am supposed to change some data in the code to match my sheet. I changed the Range ("A1:M100") to Range ("A1:N100") as I am using all the columns fro A-N. I am unsure of the other information I need to change. I am assuming I need to put in the four different words I am using to trigger the row movement I want. Where would I put them within that code?

Sorry again. I am a copy and paste man when it comes to code. I have to think about it hard when I have to find where to change/add things.

 

[rlv01]

Compile error:

Ambiguous name detected: Worksheet_change

This likely means you have more that one Worksheet_change subroutine in your worksheet's code module. The one you pasted in, and the one that was already there. You can only have one.

 

[James006]

rlv01 is right ...

You have stored two event macros with the same name ...Worksheet_change

The solution is to merge the two macros into a single one ... :wink:

 

[mikerickson]

Let me throw out this comment.
Automatic sorting routines have one big problem.
If the user changes a cell, the sheet automatically sorts, i.e. it takes that cell away and puts it somewhere else. The user cannot see if they made an error in entry, plus they have to search to find the proper place to make the entries in the other cells of the row.

You may want to reconsider whether you want this sort driven by a Change event or by a button.

 

[crawl22kp]

Awesome... Well I will take the other out and work this one and see if it does what I want then figure out how to put them together lol. Thanks for all the comments and help. I may be back on here to figure this out. We shall see