Automatically sort data using VBA

Resolved · High Priority · Version 2013

Tue 21 Oct 2014, 13:38 Delegate Laura said...

Laura has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Automatically sort data using VBA

Hi,
I've got a table in excel that covers the range B14:D160. The data in it is generated mostly from VLookups, so change when the source table is changed or added to. I want it to be automatically sorted based on the contents of column 3, in ascending order, without the user having to re-sort it manually or by clicking on a button. It would be perfect if it could all happen 'behind the scenes' instead.

I've been able to get as far as getting a macro that will do this linked to a button but not one that will operate without any user input.

Any guidance you can give me would be great! (And if possible lots of explanatory comments - I'm still quite new to this!)

Thanks

Wed 22 Oct 2014, 10:32 Trainer Marius said...

RE: Automatically sort data using VBA

Hello Laura,

Thank you for your question. Have you got code which runs the Vlookup part of the data? If so, why not copy and paste the sorting code into that macro? If not, it would be a good idea to record a macro which runs the Vlookup function on the data and then paste the sorting code into that. Excel can record and run formulas and functions.

Just remember not to paste the Sub / End Sub lines of the sorting code. Also check the code so that the correct column is sorted. I assume this is column D.

I hope this helps a bit. Please post again if not.

Kind regards
Marius Barnard
Excel Trainer

Wed 22 Oct 2014, 14:46 Delegate Laura said...

RE: Automatically sort data using VBA

Hi Marius,

Thanks for the reply. At the moment all the Vlookups are achieved through formulas in the cells in the table. I could program this but I'm not sure this would help. This would give me a macro which would pull in the data and sort it, but I think my main problem is that I don't know how to get the macro to run only when a change has occurred in the source table. I can manage to get it to run by linking it to a button that then gets clicked, but the aim is to have it occur without the user having to do anything.

Hope that made sense....

Many Thanks

Laura

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Convert Text into Number

Some times numbers maybe imported in as text or you maybe concatenating numbers that form a text string that now are treated, because you had to extract them by Text functions

To convert Text into Number just encase the relevant cell reference or formula in the TEXT function. See Converting American Date to European hint

eg TEXT(Ref) or TEXT(formula)

View all Excel hints and tips