Reading multiple cells after merged cell from excel sheet

Cells A1 and B1 are merged. Now when I read Cells A1 and B1 with Excel COM Application, they read 'Waste Accumulation'and 'NaN' and The immediate cell going down after merged cell is just A2 . Is there a method with which I can read both the cells A2 and B2 after read merged cell? I would like to read column after cells A2 and B2 separately.

[Guillaume]

"when I read Cells" Using what method? xlsread? your own code via actxserver? Please show us exactly how you do it, so we know whether the problem is with your code or elsewhere.

I've just created a spreadsheet exactly as you've described and, using the COM interface I get:

>> %... acquire reference to worksheet: ws.
>> ws.Range('A1').MergeArea.Address
ans =
    '$A$1:$B$1'  
>> %the above proves that A1 and B1 are merged
>> rg = ws.Range('A1:B2');
>> rg.Value
ans =
  2×2 cell array
    {'Waste Accumulation'}    {[NaN]}
    {'Qua'               }    {'Rev'}

using R2017b and excel 2013 (32-bit).

[Stephen23]

Ugh, merged cells!

http://datascopic.net/xlcaliber-7deadlysins/

https://www.techrepublic.com/blog/10-things/10-ways-to-keep-excel-from-biting-you-in-the-butt/

[Ghenji]

Problem is not with the code. In fact am trying to figure out if its possible to do it. Once I get the address for the string "Waste acc..." I would like to read data in next row from this Active cell. Something am working with ----

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = cellstr(fullfile(pathname, filename));
sheetname = {'Sheet3'};
string_item = {'Waste Accumulation'};
excel = actxserver('Excel.Application');
excel.Visible = 0;
workbook = excel.Workbooks.Open(fullname{1});
sheet = workbook.Worksheets.Item(sheetname{1});
start_add = sheet.Cells.Find(string_item{1}).Address;
end_add = sheet.Range(start_add).End('xlDown').Address;

code is just an example and incomplete. in short once I get to the cell address of string _item, I would like to read next immediate row and I should get data of all the cells that merged cell has covered. Like 'Qua' and 'Rev' in this case. Once I get the address of 'Qua' or 'Rev' I can further read from that column only. Have tried my best to explain if not I can come with another example.

Answer 1

[Guillaume]

I'm still not exactly clear what your question is. If you're trying to find the cells that are part of the merge I've already shown how to do that in my comment:

%...
sheet = workbook.Worksheets.Item(sheetname{1});
start_range = sheet.Cells.Find(string_item{1});  %no need to get the address
end_range = start_range.End('xlDown');
numcolumns = start_range.MergeArea.Columns.Count;  %if you want to know how many columns are merged

After that I'm not sure what you want to do.

[Ghenji]

Reading merged cell is not a problem. I want to read the row after these merged cell. look in these case --

Normally when you move downwards the iteration goes to cell A2 after merged cell. look for the address you get from these code.

start_add = sheet.Cells.Find(data_items{1}).Address
end_add = sheet.Range(start_add).End('xlDown').Address

"end_address" is always the address of the last cell of first column("Col1") after merged cell. 'A7' in this case. That means after merged cell you are always moving to "Col1" or Cell A2 in above case. How can i move to "Col2", "Col3" or "Col4" as well?

[Guillaume]

I'm not sure how your new answer differs much from what I've written above. If you wanted to acquire all the cells below the merged area then you just needed to add two more line:

%...
sheet = workbook.Worksheets.Item(sheetname{1});
start_range = sheet.Cells.Find(string_item{1});  %no need to get the address
end_range = start_range.End('xlDown');
numcolumns = start_range.MergeArea.Columns.Count;  %if you want to know how many columns are merged
full_range = get(sheet, 'Range', get(start_range, 'Offset', 1), get(end_range, 'Offset', 0, numcolumns-1);
raw_data = full_range.Value;

[Ghenji]

Oops these is giving me data from all the columns below merged cell. I just want 'Col1', 'Col2', 'Col3', 'Col4'. Only and only the data in the row exactly after merged cell and not anything after it. try the code i have written and you can see the answer.

[Guillaume]

Not having your spreadsheet, I obviously cannot try your code.

I forgot to subtract one from numcolumns. Hence why you get an extra column. Fixed now.

If you just want the one row after the merged cells:

sheet = workbook.Worksheets.Item(sheetname{1});
start_range = sheet.Cells.Find(string_item{1});  %no need to get the address
numcolumns = start_range.MergeArea.Columns.Count;
full_range = get(sheet, 'Range', get(start_range, 'Offset', 1), get(start_range, 'Offset', 1, numcolumns-1));
header = full_range.value;

[Guillaume]

Ok. Something I hadn't noticed before: when you offset a merged range by 0 columns, the returned range is offset relative to the start column, when you offset the same range by 1 or more columns, the returned range is offset relative to the end column. That greatly messes things up. Wouldn't be the first thing that makes no sense with Excel!

Never mind, we can obtain the range of the next row without using Offset:

start_range = sheet.Cells.Find(data_items{1});  %no need to get the address
numcolumns = start_range.MergeArea.Columns.Count;
full_range = get(sheet, 'Range', get(sheet, 'Cells', start_range.Row + 1, start_range.Column), get(sheet, 'Cells', start_range.Row + 1, start_range.Column + numcolumns - 1));
header = full_range.Value