Trying to remove blank cells from columns, problems with IFERROR formula.

[Big.Moe]

I'm trying to perform the simple task of removing the blank cells from two columns. I did some research & found this formula online:

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

and then adapted it to my purposes:

=IFERROR(INDEX($B$2:$B$1000,SMALL((IF(LEN($B$2:$B$1000),ROW(INDIRECT("1:"&ROWS($B$2:$B$1000))))),ROW(E2)),1),"")

That works perfectly, but when I try to do the same for the column right next to the first column, it doesn't work. Here's the formula I used for that column:

=IFERROR(INDEX($C$2:$C$1000,SMALL((IF(LEN($C$2:$C$1000),ROW(INDIRECT("1:"&ROWS($C$2:$C$1000))))),ROW(F2)),1),"")

Maybe I'm not understanding the syntax of the formula like I should and adapting the formula incorrectly?

Any insight would be greatly appreciated.

[Raphaelp]

If VBA is an option, you can take the below code and copy it into a module in your personal macro workbook. Then you can just select the columns and press ctrl+shift+R and all blank cells will be removed.

Sub Remove_Blanks()
'
' Remove_Blanks Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
End Sub

[Special-K]

Use this instead, it may need tweaking, post your actual file if this doesnt work

=IFERROR(INDEX($C$2:$C$1000,SMALL(IF(($C$2:$C$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter

[Big.Moe]

Use this instead, it may need tweaking, post your actual file if this doesnt work

=IFERROR(INDEX($C$2:$C$1000,SMALL(IF(($C$2:$C$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter

Hmmm....your formula does the same thing. Must be something within my spreadsheet.

Here's my file & the sheet in question is "REF CALCS". My apologies, I attached the wrong file on my original post.

If you have a minute to take a quick look, I would be very appreciative.

[Jonmo1]

There are error values within Column C, beginning at C531.

If I clear the contents from that row and down, then the formula works as expected.

[Big.Moe]

There are error values within Column C, beginning at C531.

If I clear the contents from that row and down, then the formula works as expected.

Jonmo1, you are a genius....and I am a total tool!

I can't believe I didn't even take the time to check all the way down the range. My apologies for wasting you guys' time for such a stupid mistake......but many thanks nevertheless!

[Jonmo1]

You're welcome.