I am trying to combine data from other sheets into a single sheet. I thought I could use a formula to find the last row in each sheet and combine with an Indirect ArrayFormula but that doesn't work.
Indirect("ARRAYFORMULA({'Sheet1'!A2:J"&ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet1'!A1:A10000)), ROW('Sheet1'!A1:A10000),0)))&";'Sheet2'!A2:J"&ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet2'!A1:A10000)), ROW('Sheet2'!A1:A10000),0)))})")
The Indirect and ArrayFormula combo doesn't seem to be the answer as it errors out - it appears you can't run an indirect and array formula together.
EDIT: Using the formula above without the Indirect and finding the last blank row will combine the sheets when I keep a long row range but it keeps all the blank rows and spreads the data of each sheet out too much.
ARRAYFORMULA({'Sheet1'!A2:J100";'Sheet2'!A2:J100)})
Which is why I was trying to use the max arrayformula to find the last row in each sheet to consolidate the blank rows.
ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet1'!A1:A10000)), ROW('Sheet1'!A1:A10000),0)))
Since I misunderstood your question, here's an alternative to the formula you provided..
=QUERY({Sheet2!A:J; Sheet3!A:J}, "select * where Col1 <>'' ")