How do I reference all non empty cells in a column (ex: all non empty cells below A1)

Question

How do I reference all non empty cells in a column (ex: all non empty cells below A1).

I have multiple sheets that have the same header but the data is of variable length on column one.

Let say each sheet will have the data on column C below C1 which is always non blank

I need to automatically get these copied in another spreadsheet that I manually point to this data sheet.

Ex: I work on Sheet1 , my data is in Sheet2 column C, C1 and the below, variable number of non blank cells

How do I copy automatically all data in Sheet2 from Sheet1.

Filter out blanks and copy visible cells? There are good examples on SO of how to do this. Or if you're just trying to find the last row, see https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — BigBen, Apr 10 '19 at 18:21

score 1 · Accepted Answer · answered Apr 10 '19 at 18:53

1

With data in Sheet2 column C:C like:

In Sheet1 cell E1 enter:

=IFERROR(INDEX(Sheet2!C:C, AGGREGATE(15, 6, ROW($1:$999)/(Sheet2!C:C<>""), ROW(1:1))),"")

and copy downward:

answered Apr 10 '19 at 18:53

Gary's Student

1 Answers1