Without VBA, SUBTOTAL(OFFSET may help a lot with formula based option
But it is volatile formula, and cause the PC become slow, for large data.
With VBA, I believe there is no reason to use that complex formula combination.
If you are willing to get better VBA solution, try to upload a sample worksheet.