Create a non-contiguous range

[MrPez]

I am trying to create a range to use linest. My data is not continuous and some entries may be errors/zeros. So I need some way to interrogate the data and only include the actual numbers in the range.

For example, say my potential data is in M33:M42,M47:M52,M56:M60 but only M33,M34,M35,M47,M48 actually contain values. The rest are #DIV/0 or #VALUE! errors due to other data being missing.

Any idea how I can accomplish this?

 

[XOR LX]

@shg

Impressive that you're remaining faithful to Excel for such higher applications!

Regards

 

[flywire]

I'm not clear on using the CloseRows macro. As I read it the data has to be in adjacent columns.

I have years 2001 to 2018 in column A, data in columns B:G and Data in Column H with half the cell blank. I want a linest for year 2020 using Column A and H.