Check if numbering of formula is ordered

[MasterBash]

Hello,

On a sheet I have this following formula in A1 :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ColumnsToText(OtherSheet!$J1:$X1),"^^^^^^^^^^^^",""),"^^^^^^^^^^^",""),"^^","")

This is being filled all the way to the last row that contains text. In other words, this is changing the Text to Columns to the original text (Columns to text) after doing modifications to the Text to Columns version of the text. Sometimes this involve deleting lines and people forget to re-apply the formula all the way to the last row containing text.
I am wondering, how can I make sure the numbering is correct and it isn't skipping a line? ($J1:$X1)

As an example with the formula above :
($J1:$X1)
($J2:$X2)
($J3:$X3)
($J5:$X5)

In this case ($J4:$X4) is missing.
Is there a formula (ideally) or if not possible, a vba script to make sure that every line is there ?

Thank you.

 

[Scott Huish]

Not sure what that formula is supposed to. Can you provide a sample of what you are working with and what it's supposed to be?

But you're already using VBA for your custom function. Why not just have them run a macro that does everything?

 

[MasterBash]

Actually, as you wrote this, I am working on something like that. I am now using Power Query to pull the data, so it is now totally different from my initial post.
Unfortunately, I am unable to paste the data as it is confidential data used for a data management system.

Sub CorrectFile()

Dim Lastrow As Long
    Lastrow = Worksheets("OtherSheet").Range("J" & Rows.Count).End(xlUp).Row
    Worksheets("Text format").Range("A1:A" & Lastrow).Formula = "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ColumnsToText(OtherSheet!$J2:$X2),"^^^^^^^^^^^^"','""),"^^^^^^^^^^^",""),"^^","")"
End Sub

So the Power Query table is on "OtherSheet" and I am looking to count the rows from the Power Query table (first column being J).
Apply the formula to the Text format sheet from A1 all the way down to the Lastrow count from OtherSheet.

It is supposed to apply the formula above... I guess, I didn't have a chance to try. Problem is there are so many ^, " and , inside the formula that VBA keeps throwing an error. When I use it as a standard formula (no VBA), it works great, but I got to manually fill down. I wonder if there are an easy way to simply paste my formula using VBA.
^ being a delimiter. Problem is that for empty "cells" (It is supposed to be 15 columns wide, but some rows are less than that), it is automatically adding multiple ^ next to each other, which is what the substitute formula is doing - simply removing them.

1^texthere^texthere^texthere^^^^^^^^^^^^
2^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere^texthere

Maybe it is an odd way of doing it, with a bunch of nested Substitute, but it does work. I just wish to apply it using vba if possible.