Can formulas be ignored?

viper

Active Member
Joined
Feb 15, 2002
Messages
382
How can I make my sorting macro ignore the hidden formulas?

My macro sorts fine and includes everything I want but I have extra cells for adding new data that contain a formula. When my macro sorts it places all empty cells at the top of the list then the data. When I delete the hidden formulas the sort places the empty cells at the bottom of my list like I want. Is there any way to code my macro to treat all cells that contain only the formula(not the data) as empty? The only thing the formula is, is a reference to another sheet ie. ='Sheet6'!$A$6.

Thanks,
 
After all the time I worked on this I found a solution that works and the user is happy with it.

I used the following formula: =VLOOKUP(C4,$B$43:$D$73,3,FALSE)

Since I needed the errors to be in the cell for it to sort to the bottom, I didn't need to evaluate if it contained a formula. Also, with the conditional format alternating rows with white/gray I couldn't change the font color of only the error rows without making a mess of all of the other CF. So, with the user's permission, I added a CF to that column only that would change all fill and font with the same shade of gray if the cell value is an error. Now all of the gray sorts to the bottom.

This may not be the best solution, changing the font color of the error cells, but it works and that is good enough for me.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi, Xenou

I tried the ZZZ much earlier in my process but it still left me with the problem of making the text blend in. Just formatting the text in white didn't solve the problem because of the alternating white/gray backgrounds.

Basically, I did have to force the text the blend in, but I just made it all gray and it blends much better than all white. My user is happy and that makes me happy, too.

Thanks again!!!

:LOL:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top