Expand Single Row Range References

I couldn't find a solution to my problem anywhere online, so I thought I'd post up the one I came up with.

My problem essentially stems from accidentally deleting too many rows. I know that many frown upon deleting rows and recommend clearing them instead, but for a variety of reasons, in my particular case, I needed to delete them. Incidentally, this is done programmatically on a number of different sheets.

The sheets in question have a variety of sometimes rather complex formulas in the first several rows, which compute on data in lower rows. By inserting and deleting rows, the references are automatically expanded and contracted. Unfortunately at one point I "reset" the data, and deleted all of the data rows but the very first one, and all of those references now pointed to a single row. E.g. I went from something like A1:=SUM(A2:A25), to A1:=SUM(A2:A2). Now there was no way to get those single row references to expand. Unfortunately I didn't notice this as an issue for quite some time, and in the meantime I'd made quite a few changes that precluded the option of copying the formulas from an older backup of the file.

I wrote the following VBA to correct the problem. It uses regular expressions to find any ranges from a single cell to the same cell. It finds and preserves any combination of absolute and relative references. As written it expands the range by one row, which could be changed fairly easily to a larger static number, or something dynamic. This could also be modified to take said range and move the second reference over by a column instead. I think this would be easiest by switching to R1C1 notation and rewriting the regular expressions. Which, I should point out, this is currently for A1 style references only!

Please Login or Register  to view this content.

It's kind of sloppy, with little in the way of error checking, but it was meant to be a tailor to suit solution. Hopefully it may help someone in the future.

Thank you for posting this.