1

I have an importrange formula as below which references multiple ranges, calling the sheet URL's using the 'L Links'!Bx, reference, each 'book' having an identical sheet set. (There are actually 90 URL's but I have only shown part here.) It works fine, except, it brings back all the blank rows, and the ranges vary in length.

Is there a simple way of removing the blank rows in the formula en masse?

={importrange('L Links'!B3,"'Donor Projection Input'!A2:H");importrange('L Links'!B4,"'Donor Projection Input'!A2:H");importrange('L Links'!B5,"'Donor Projection Input'!A2:H");importrange('L Links'!B6,"'Donor Projection Input'!A2:H");importrange('L Links'!B7,"'Donor Projection Input'!A2:H");importrange('L Links'!B8,"'Donor Projection Input'!A2:H")}

1 Answer 1

0

From the question

  1. Is there a simple way of removing the blank rows in the formula en masse?

Yes, actually there are multiple ways. Try using QUERY.

The following example assums that the first column is not blank for the rows that the OP wants to keep:

=QUERY(
{importrange('L Links'!B3,"'Donor Projection Input'!A2:H");importrange('L Links'!B4,"'Donor Projection Input'!A2:H");importrange('L Links'!B5,"'Donor Projection Input'!A2:H");importrange('L Links'!B6,"'Donor Projection Input'!A2:H");importrange('L Links'!B7,"'Donor Projection Input'!A2:H");importrange('L Links'!B8,"'Donor Projection Input'!A2:H")},
"SELECT * WHERE Col1 is not null")

1
  • Works good- Thanks for your help!
    – Castleman
    Dec 16, 2020 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.