Hannah-jane has attended:
Excel VBA Introduction course
VBA Sort
VBA to sort data A-Z with blanks appearing after Z (not at the top) without having to delete. Thank you!
RE: VBA Sort
Hi Hannah-jane,
Thank you for the forum question.
I just tried to record sorting a column in ascending order and this gave me the blanks at the bottom. Do you have the blanks in the same column as you sort?
Excel will also give you the blanks at the bottom if you record sorting in descending order.
Please let me know if I have misunderstood what you need.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: VBA Sort
Thank you for your speedy response Jens. I fear I was not specific enough...
I have a column with IF formulae that return blanks "" in certain instances.
My recorded Macro copies this column, pastes it into a new column, removes the duplicate and then sorts in alphabetical order.
I always have a blank cell at the top which I am assuming is because of the "" in my earlier formula?
Thanks again,
Hannah-Jane
RE: VBA Sort
Hi Hannah-jane,
First of all sorry for the late answer, but I have been out of office.
I have found a way of doing what you want. The problem is that if the cell is not empty you have the problem. The first I did was to copy the range and then paste it as values only. This will remove the IF function and the cell will be empty. Then I could do the sorting and get the blanks at the bottom. After I added the IF functions again.
If you have a look at the attached workbook you will find my example. I recorded most of it and the I added the loops.
You will need to amend the code.
I hope this can help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
