As per another question of mine, I have to read hundreds of sheets with ranges that start at B35 but have no specific number of rows.
However, one thing that really shows where my needed range ends, is the word TOTAL
So I tried a simple Lookup Range from B35 to B100 in a single sheet, just to test, sending the result to a string variable and then, outside Excel App Scope, showing that String Variable in a message box (just for test and checking if it’s working)
But no… the Message Box appears empty.
However, the TOTAL word I am looking up is inside a 5 columns merged cell.
When I unmerged the cells, the Message Box found the cell location (B44 in the specific sheet I was using for test)
Well, obviously there is no way I will try to unmerge hundreds of sheets, right?
Why not set the range of your read range activity to just “B35”. It will read your data starting B35, then just add some data cleansing or validation. (i.e. remove other columns, if rowvalue = total then skip)
Ok, but if it’s not working with the merged cells when I do b35:b100, why would it find and skip the row with TOTAL when simply starting the range from B35?
also, i need to copy all the ranges (which always start at B35) from hundreds of sheets to a single sheet… one range below the other at the destination.
Thus I need to know the total rows copied with each range… is that possible without counting one by one?
When you read a cell when they are not merge you get the value of that cell without a problem, so what happens when you merge a cell. I hope I explained it well in the below picture:
Hi Rmunro. I am aware of that. I suppose you are explaining that to me because I am talking about column B (and in your example you talk about the value being at A)
But B is the first column of the merged cells. And is the one where I am trying to find the value.
The merged cells are Bx, Cx, Dx, Ex, Fx, Gx, Hx and Ix. (where X represents the row number, which is a variable number.)
That means the value (the word “TOTAL”) should be at Bx. When I unmerge the cells, the value IS located at Bx.
And yet, when I try to find TOTAL in the merged cells, looking at Bx, I do not find it. But unmerging it, I find it at Bx.
here, a screen capture of the Excel form… notice that A column was used for margins. It doesn´t contain anything. The first merged cell is Bx.
Using ReadCell and pointing to the merged cell, it worked just as in your example, and just as in my Lookup test with unmerged cells.
However, LookUp range still doesn´t work. It gives me a blank output, instead of the cell address (which should be the same one I read following your example).
So the thread question remains: does LookUp Range work with merged cells? Apparently, it does NOT. It works with unmerged cells (tested) and ReadCell works with merged cells.
@Rogerio_Penna, Lookup Range works if you specify the range correctly including the merged portion of your cells, in your case the merged cell is B48 - I48.
You can either specify the range for lookup to be “B:I” which will lookup all rows from column B to I or if you know the rows beforehand, specifying “48:48” will lookup all cells in row 48, including column B to I.
Similarly, if the cells are merged vertically, e.g. cells B48 - B51, using range “B:B” or “48:51” should give the address.
The idea is to specify the range that covers the merged cells as well.
