Forum Discussion
Merged Cells with XLOOKUP Help
Note: this code assumes there are NO blank rows within the data range, and the target range to be cleaned is only the first 4 columns.
VERY BIG assumptions, given what IS visible in that spreadsheet. That said, it's nice to know there are VBA routines that could be used to clean up such starting points.
mathetes Yes, I made a couple of assumptions, hence the warning at the bottom to test the code on a copy of the workbook. Having said that, it's pretty clear that the data starts in cell A1 and fairly safe to assume the first 4 columns contain the merged cells. If blank rows are present, the worst that could happen (with the CurrentRegion method) is that only a portion of the data would be cleaned (or nothing at all, if the first row in the table was blank). If that's the case, a simple adjustment can be made to achieve the desired result (ie: change ws.Range("A1").CurrentRegion to ws.UsedRange), or additional code can be written to remove the blank rows altogether. 🙂
Riny_van_Eekelen This is pretty straightforward code that took me less than 5 minutes to write, as I've used methods like this to clean data many times before. I'm not very skilled with Power Query, though, so would love to see a solution... please share. 😉