I am having difficulties with sorting my ranges alphabetically. Please see the attached file to see the structure of the data. I have a user form that inserts new rows at the bottom of each month and I need to sort column "A" each time a new record is inserted.
Many thanks!
Last edited by Bob@Sun; 01-13-2010 at 03:40 PM.
I would think this could work, it uses AutoFilter, selects the Non-Blank cells then sorts
Please Login or Register to view this content.
This code does not sort the right way.
I need each range to be sorted separated: A2:D8; A10:D16 ets.
The code below is part of a user form and adds new row with the value of textbox. I need line that will sort ranges after the new row is inseted.
Please Login or Register to view this content.
Hello,
When your code insert a new row the Event Worksheet_Change call the macro SortRanges.
1) Copy this code in standard module
2) Copy this code in the particular Sheet code windowPlease Login or Register to view this content.
Please Login or Register to view this content.
Best regards.
PMO
Patrick Morange
PS : Please, tell me if my language is correct.
Well this code gives me an error '1004' This Operation requires the merged cells to be identically sized,
I do not have any merged cells but have cells with different size. If that is the reason for the error, I need another approach to the problem as i cannot make all rows with the same size.
Besides that, I need this code to run only when I use the user form to insert new row.
As am running another macros on the sheets where my using Worksheet_Change event is not good idea as it runs each time I am doing something else.
I will appreciate some other ideas!
Thanks!
You example did not have merged cells, merged cells are very hard to work with and it would be best if you got rid of them.Originally Posted by Bob@Sun
If you think you don't have any merged cells, then select all the cells on the sheet and do Format > Cells > Alignment, untick Merge cells and try again.Well this code gives me an error '1004' This Operation requires the merged cells to be identically sized,
I do not have any merged cells but have cells with different size.
Entia non sunt multiplicanda sine necessitate
Actually the merged cells are not the problem in this case.
As I explained, the approach in the code above does not suit my needs.
I need a code that does not use the Worksheet_Change event because am i running different macros on the same sheet where my data is. That is why I need I code which will Sort the data only when I new rows are added to the range.
I hope I explained well.
Hello,
If your problem with merged cells arranged, here a new version with the code run when you use your UserForm
1) Copy this code in standard module
2) Create an UserForm1 with a CommandButton1 and copy this codePlease Login or Register to view this content.
With regards.Please Login or Register to view this content.
PMO
Patrick Morange
We are almost there!
This code works ok, but needs a little change. When names in Column A are rearanged I need the information and the formating of the cells in the range B:AF to be aranged properly.
I am attaching a sample file. Many thanks for the help!
Hello,
A new version with the following code
WARNING !Please Login or Register to view this content.
To particularize the cells do not use diagonal borders for the cell format does not follow the sort. Instead, use a background color.
Best regards.
PMO
Patrick Morange
Ok, in this case can you help me to change the code and use background color instead of diagonal borders.
That is my old code
I tried to change it toPlease Login or Register to view this content.
but it does not work properly.Please Login or Register to view this content.
I figured it out:
I will try now your code to sort the range and will see if everything is ok.Please Login or Register to view this content.
Hi Patrick,
Your code works perfect!
Thanks for your help!
hi Bob@Sun , Please help me
i want to sort column A2:A , B2:B and C2:C...xx:xx
thanks
Hi, sunblue,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks