VBA Sort

Resolved · Low Priority · Version 2013

Thu 7 Jul 2016, 16:44 Delegate Hannah-jane said...

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!

Fri 8 Jul 2016, 09:35 Trainer Jens said...

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

Tue 12 Jul 2016, 18:31 Delegate Hannah-jane said...

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

Mon 18 Jul 2016, 11:36 Trainer Jens said...

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

Attached files...

sort VBA.xlsm

Mon 25 Jul 2016: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Use Paint Brush to Format More than one Cell

To copy a format to many cells or ranges

1. Select the cell with the format that you want to copy

2. Double-click on the paint brush

When you move to the cell where you want to paste the format you will notice that a paint brush follows the cursor.

Paste the format to all the cells or ranges you need to format.

When finished go back to the paint brush on the tool bar and single click on it to deactivate the function.

View all Excel hints and tips