Copying non-blank cell data into another sheet

Copy, no. Reference with a formula, yes. Can you be more specific?

For example, if you put this formula in A1 and copy to the right and down, it will show all non-blank values in Sheet1.

Formula:

Please Login or Register  to view this content.

Hard to tell if that's what you mean.

Or, you could apply filters, (ask, if you dont know how to), filter on non-blanks, copy what remains to the new location, remove/undo filters. You could even record a macro to do that for you

Here is example of VBA code

Please Login or Register  to view this content.

This code filters data in Sheet 4 as per criteria cell is blank in column 3 and copy paste selection to sheet5.

Regards,

Tanja

Try this...

Sheet1

	A
1	Header
2	Data1
3	Data2
4
5
6	Data3
7	Data4
8
9	Data5
10

Sheet2

	A
1	Header
2	Data1
3	Data2
4	Data3
5	Data4
6	Data5
7

This array formula** entered in A2:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$2:A$10<>"",ROW(Sheet1!A$2:A$10)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

You would need to extend the range in the formula. In the sample formula I only reference down to A10. Extend this as needed.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.