I have this formula:

=QUERY(
    {AG4:AW101},
    "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17 
    where Col1 is not null
    and Col1 is not null
    and Col2 is not null
    and Col3 is not null
    and Col4 is not null
    and Col5 is not null
    and Col6 is not null
    and Col7 is not null
    and Col8 is not null
    and Col9 is not null
    and Col10 is not null
    and Col11 is not null
    and Col12 is not null
    and Col13 is not null
    and Col14 is not null
    and Col15 is not null
    and Col16 is not null
    and Col17 is not null
    order by Col"&$N$1&" "&$N$2&"")

Is there anyway to make it shorter? Do I really need to repeat and ColX is not null for every column?

CodePudding user response：

join them. lets say your range is A:F and you want to check B:F for is not null, then try:

=ARRAYFORMULA(QUERY({A:F, TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9)))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6
  where Col7 is not null", ))