Sort columns ignoring blank cells

Hello,

Can someone pls assist me in providing a macro that is able to sort a column and ignore all blank cells in that column? I have noticed many macros I've tested allow the blank cells to go to the top of the column after it has been sorted. As such, several criteria would need to be addressed:


1) There are a total of 20 columns on the worksheet that would need to be linked to the sorting being done in one column (column T).

2) The sort in column T would start from cell T8 until T5000 and would be sorted in descending order.

3) Most importantly, the sort must ignore blank cells and allocate them below the last data entry in column T. The blank cells will have formulas embedded in them.

I've reviewed many macros currently available that purport to ignore the blank cells but not one has worked. My suspicion is that they are failing because even though the cells are blank, they have formulas embedded in them.

Thank you in advance.

Hi maldonadocj

I don't think you need a macro! The problem is with the way Excel sorts the NUL character - I assume you have formulas which insert a "" if the test fails.

The easiest way to deal with this is to have the formula return 0 instead of NUL (""). If you don't want to see the 0s, just have a conditional format to make the number white if it is zero.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hello David,

Thank you for your response. I had already revised the formulas to have a "0" or an "-" and still had the problem of these cells going to the top of the column after the sort. Additionally, I do need a macro as it is requirement to minimize the usage of formulas given that the spreadsheet already has a significant amount of formulas.

Accordingly, can you pls. assist me in creating the macro?

Thank you

OK maldonadocj,

But it would really help if I could have a copy of the workbook.

Please attach a copy.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

Further to my earlier post requesting a copy of the file.

If you placed the zero INSIDE quote marks (as was implied in your post #3) your sort would place the rows at the top, because it would have been interpreted as text.

The 0 must NOT have quote marks so that it is seen as a number.

This is NOT adding to your formulas, it is really only changing how one of them behaves, and should have no impact on your processing speed.

If you don't want to post a copy, display the formula in column T so that I can see what it is doing. It might give a clue to the reason for your sort issues.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hello David,

I am also open to the idea that the formula in column I could be revised so that the sort function works properly by ignoring the blank cells; but still having the cells with no data remaining blank (i.e. not having a zero or delimiter as a place holder).

Thanks again.

Hello maldonadocj,

You have confused me somewhat because what you are saying is inconsistent with your posted example.

In your first post, you spoke of sorting by Column T, but in your file, you are sorting by Column J. In addition, in your last post, you said that the sort is on Col I, but it is actually J. (I assume you have added a column!)

The macro in the attachment works on the file you posted. You will have to adapt it for the real circumstances.

I hope this helps, please let me know!

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hello David,

Apologies for changing the columns being referenced as you are correct that I added an extra column and didn't account for that when I asked for the sort to occur in this column.

I reviewed the code you sent me and it appears to work when sorting column J from smallest to largest but not when sorting from largest to smallest. When sorting the data largest to smallest in the spreadsheet, the blank rows were at the top again.

Can you pls. review the code to see how this can be addressed?

I appreciate your time and patience with this inquiry.

Thank you

I don't understand what your problem is!

When you are sorting Largest to Smallest (Z to A) you just click on the button I provided. This sorts and places the blanks at the bottom.

If you are going the other way, you don't need the macro - just use the Excel Sort feature on the Data Ribbon.

As I said in Post #8, this macro will only work on the file you sent. You will have to adapt the code if the sheet changes.

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

When you reply please make it clear WHO you are responding to by mentioning their name.

Hello David,

Thank you. I will work on adjusting the code on my end.

Have not heard how you got on! I will be away for a few days, so will be unable to provide a prompt response.

All the best,

David

Hi David,

I was able to work out the issues. Thanks again for your assistance.


Sent from my iPad using Tapatalk