Attached is an Excel document, Excel_EqualWidthCells.Xlsx
that has two sheets, one labeled "Structure".
The other is labeled, "Header-Footers", and looks like this:
Headers Footers -- There is inconsistent use of "…", so assume that "…Begin" and "Begin" are keywords
…Begin …End -- Notice the "…" acts like it is a singe char
…If -- Assume case insensitivity, so "BEGIN" and "begin" are also matches
For simplicity, I can remove the "…" from the document. Other tables have different keywords for their headers/footers, so I'll just change the corresponding headers and footer keywords in the VBA code.
The attached Excel_EqualWidthCells---ManualInden.xlsx
document shows the desired result after the indentation macro is run.
file is very flat looking, yet describes a complex structure using headers and footers. I manually indented sections of rows so that I could see the structure. This is tedious, but doable.
One suggested approach in a different question is found here.
"This can be done with a stack structure, where you push a set of items on the stack when some condition is detected and remove them when another condition is detected. In your case the first set of conditions is a cell that begins with ("loop", "if") and the second set of conditions is a cell that begins with ("end"). If you're working in Excel, you might use the stack 'level' (depth) as a parameter in an .Offset() method.
"With as simple a problem as this one, your stack structure can be an integer variable. You might indent as you iterate the rows."
I am hoping that a macro can look at the headers, and when a footer is found, then it is associated with the previous header row number at the top of the stack. Having the two row numbers, the macro can indent the rows one column to the right (and then pop the stack to throw away that header information.)
This question is much simpler than a similar question because the excel spreadsheet has no merged vertical cells. I am told this makes indentation much easier to do.
: The headers have horizontal merged cells, which sometimes required me to move the header to an open area, then indent the rest of the range, and then move the header back to the correct position. This only occurs if there are two or more headers in a consecutive rows. Then the inner header needs to be moved to a cell that is merged, and when I was unable to do this directly. But other one line "indent" macros (in other questions) seemed to have no trouble moving a merged horizontal header or footer row to the next column.
Attached is the Excel_EqualWidthCells---ManualInden.xlsx
, which is the result of my manual, no-macro, indentation. When the macro-driven solution is done, it should be easy to compare results to see whether I made an indentation mistake or not.