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,
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi Viper


Push F5 and use the Edit>Go to Special - Formulas to select your formula cells, then hide the rows. Excel will not sort hiiden rows.
 
Upvote 0
Dave,
Thanks, but I do want them included in my sort. They are part of a group. So can I go at it differently? Can I change my code to sort only those in Range("A6:C10,A12:C40") <> "", or will the sort still pick up the hidden formula?
 
Upvote 0
add a space like this:
=" "&'Sheet6'!$A$6

and the formula cells should drop to the bottom of the list
 
Upvote 0
You the man!! Worked great. Is there anyway to prevent the empty cell from showing #value? I tried =IF(ISERROR(""&'Team Entry'!A5),"",""&'Team Entry'!A5) in each cell but that seemed to cancel out the space and put the empty cells on top again after sorting.

But thanks for the information.
 
Upvote 0
(ISERROR(""&'Team Entry'!A5),"",""&'Team Entry'!A5) has no spaces in it. Try:

(ISERROR(" "&'Team Entry'!A5)," "," "&'Team Entry'!A5)
 
Upvote 0
Thanks Nate,

Still puts empty at top. I even did
=" " & if(iserror(" " & 'sheet'!$A$5)," "," " & 'sheet'!$A$5) but still no effect.

But thanks for all your help. I'll work on it somemore, I've got until 4/2/02 before I roll it out.
 
Upvote 0
I have the same conundrum. In cells in Column D, I have the following formula:

=IF(ISERROR(VLOOKUP(C4,$B$43:$D$73,3,FALSE)),"",VLOOKUP(C4,$B$43:$D$73,3,FALSE))

It works fine but when I sort the column, all the blanks sort to the top. So, I modified it to:

=IF(ISERROR(VLOOKUP(C4,$B$43:$D$73,3,FALSE))," "&VLOOKUP(C4,$B$43:$D$73,3,FALSE),VLOOKUP(C4,$B$43:$D$73,3,FALSE))

based on the above suggestions. I now get #N/A as a result when there is no value to pull in, which isn't a problem except that I don't want it to show as such. I went to the Page Layout tab, Page Setup, Sheets and asked that errors display as <blank> but the #N/A is still visible. I have the cells shaded using Conditional Formatting =Mod(row() so that alternating rows are white/gray. I have tried to modify the CF so that if the row has #N/A the text will go white/gray but I can't make it work either.

Does anyone have another other suggestions?
 
Upvote 0
Perhaps your CF Criteria needs to be " #N/A" rather than "#N/A" - if we are prefixing a space to the result.

Another idea (a bad one maybe) would be to use "ZZZ" as the value for "not found" results - so it drops to the bottom of a text-sorted list. You might then use a CF to conditionally format ZZZ as White to "blend in" with the background.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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