Control Shift Copy Picking Up Iferror Blank Cells

[longtail18]

Hello - looking for some help / advice on the following.

I have added an iferror to my index match to return results of 0 as blank; however, when I control shift copy it picks up all cells with the formula regardless if the cell contains a number or blank. I would like a way to only then copy the cells which contain a number, and not all cells that contain the formula.

Is there a way to do this? Would appreciate the help - thank you!

 

[MARK858]

If you mean the"blanks" are at the end of the data then you can do it with VBA but we would need to know the Column/s or Range you are copying from and the destination.

 

[MARK858]

Or you can use Specialcells...

Select the range, click Ctrl + G, click Special, then check Formulas and make sure that numbers is the only item checked under it and click OK.

 

[longtail18]

I have included an image to help explain...

[/IMG]


I would like to copy down to E5 and then over to B5 to select the area that has a value in units ordered. Instead, because the iferror formula I select through to E7 and over.

Was trying to see if there was a way to only select down to the area that has a number in the units ordered column.

Hope this helps clarify - thanks

 

[MARK858]

Sub SelectBE()
Range("B3:E" & Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row).Select
End Sub