Dynamic sort a range of cells and its descriptions

[Rocky2013]

Hello,
I have a table with two columns shown below. the tablet rows holds the names will be 99 rows (A2:A100).
I need a VBA or function if I add a new name in B6 and description in C6, the dynamic sorted name list
(sorted by alphabetically from A to Z) will be dynamically sorting including its description. I prefer a function
or VBA without a command button if possible, otherwise I am OK with a "Sorting" command button.


Thanks


Col 1.......Col B...............Col C
A1..........Name...............Description
A2..........Amazon............On-Line shopping
A3..........Yahoo...............Web site
A4..........Stop&Shop.........Supermarket
A5..........Macy's...............Department store

[Arkadi]

The only way to do it without buttons is to either make it sort when something changes, I assume you enter description last, so this code would only do something when a new description is entered. The code needs to be in the worksheet object, not in a normal module.

Please Login or Register  to view this content.

[Rocky2013]

Hello,
Thanks for the reply. It works.

Thanks

[Rocky2013]

Hello,
I change your code to a range (A2:B20) instead entire columns. I am not sure if I did it right.

I did some testing for fool proof, I found some issue (may not an issue)
1. Add entry to row A20/B20, and above, the new add will be sorted dynamically. WORKING
2. I delete new texts in test 1. Then I add entry to row A21/B21, it does not sort. WORKING
3. I keep the the texts in A21/B21. Then I enter texts to A20/B20, then row text in A20:B21
sorted and moved up including color fill in A21/B21.

Note: The color fill on my final file is not presented. I use that for visual to indicate the row and
any rows below should not be sorted.

In my spread sheet, I may have other information that are not part of the sorting.

I am looking for if there is away in the code to prevent the text in A21/B21 and
beyond from dynamic sorting if a new entry is enter above A21/B21.

[Rocky2013]

Hello,
I change your code to a range (A2:B20) instead entire column. I am not sure if I did it right.
I did some testing for fool proof, I found some issue (may not an issue)

1. Add entry to row A20/B20, and above, the new add will be sorted dynamically. WORKING
2. I delete new texts in test 1. Then I add entry to row A21/B21, it does not sort. WORKING
3. I keep the the texts in A21/B21. Then I enter texts to A20/B20, then row text in A20:B21
sorted and moved up including color fill in A21/B21.

Note: The color fill on my final file is not presented. I use that for visual to indicate the row and
any rows below should not be sorted.

In my spread sheet, I may have other information that are not part of the sorting.

I am looking for if there is away in the code to prevent the text at A21/B21 and
beyond from dynamic sorting if a new entry is enter above A21/B21.

Thanks

[Arkadi]

"If Not Intersect(Target, Range("B2:B20")) Is Nothing Then" this is the area where a change should trigger sort.
"lr = Range("B" & Rows.Count).End(xlUp).Row" this is used to sort later, it finds the last row with data in B, even if that is row 1500

"Range("A1:B20" & lr).Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes"
Ok so this is no good.... you can't say Range("A1:B20" & lr) so maybe change that to:

Please Login or Register  to view this content.

[Rocky2013]

Hello,
Wow! You are good, It works! you make it very simple.

Thanks

[Arkadi]

My pleasure! glad I could help