Row search with some merged rows

[Luke777]

Hi all!

I've got a bit of code that essential scans rows for a value. Looks a little like

For each cell in Range(cells(searchRow,3),cells(searchRow,lastCol))
If cell.value = ...

Once the value is found and I've done whatever with it, I then +1 to searchRow. Simple enough right?

well, when it comes to actually searching rows for cell values, I've noticed that if any cells are merged with the row/s beneath, the results are wonky.

To keep it simple, if I search from row 1 for the word "Pingu" and A1 contains the word "Pingu" I have no problem.

If I search row 1 for the word "Pingu" and A1 is merged with A2 and contains the word "Pingu" I have no problem.

However, if I search row 2 for "Pingu" and A1 and A2 are merged and contain "Pingu", "Pingu" is not detected because it seems like A2 no longer exists.

Is there a way around this other than unmerging cells?

Thanks all

 

[Marc L]

Hi,​

​

as a reminder only the first cell of a merged cells range contains a value​

so this is why when Excel is badly used as a database software it is recommended to never merge cells.​

​

As your code follows the slowest way then you can check the first cell of the MergeArea property …​

​