0

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")}

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")

  • Works good- Thanks for your help! – Castleman Dec 16 '20 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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