How do I select several rows in Excel?

I am trying to find the quickest way to color thousands of rows in an Excel spreadsheet. My current method involves creating a cell array with strings, limited by the number of characters, of all the Excel cells that need to be colored, and then applying the appropriate colors at the end with several loops. If there is a way to use Union within Matlab, or to select thousands of non-adjacent rows within Excel, so that I can apply colors in one go without a loop, I'd greatly appreciate any suggestions!

Summary of current code:

xls=actxserver('Excel.Application');
xlsWB=xls.Workbooks.Open(FULL_XLS_FILE);
xlsWS=xlsWB.Sheets.Item(1);

rangeidx=cell(1000,10);
rangeidx(:)={''};

j=ones(1,10);
strlimit=240; %max for Range input is apparently 255 char, assume cells reach 6 digits

% This is one section of the loop for one coloring scheme, which is one
% string segmented into 255 char limited chunks.
for i=2:size(FINAL,1) %cycling through every row for color index
    if coloridx{i} == 1 %for one coloring scheme
        if coloridx(i-1) == 1 %for adjacent rows
            rangeidx{j(1),1}= rangeidx{j(1),1}(1:end-(numel(num2str(i))+1)); % removing last row number and comma
            rangeidx{j(1),1}=[rangeidx{j(1),1} sprintf('%g,',i)]; % adding second row number for previous column I
        else %regular input
            rangeidx{j(1),1}=[rangeidx{j(1),1} sprintf('A%g:I%g,',i,i)]; %storing syntax for rows to be colored
        end
        if size(rangeidx{j(1),1},2) >= strlimit %for max Range string input
            j(1)=j(1)+1; %cycling index for next rows
        end
    end
end

% One out of many loops for coloring all the rows. The Range() part is what
% limits the chars for the strings listing the rows. This line is what
% takes the most time, and I'm trying to find a method that doesn't require
% it to be looped.
for i=1:j(1) %for one color
    if size(rangeidx{i,1},2) >= 6 %minimum length for one input
        rangeidx{i,1}=rangeidx{i,1}(1:end-1); %getting rid of comma
        xlsWS.Range(rangeidx{i,1}).Interior.Color=rgbGreen;
    end
end

Answer 1

[dpb]

You can use any valid range expression to address any range you can code the logic to compute addresses of.

You must then use either a string variable or a char() string in the call with COM, however, a cellstr() will fail.

An example of some code I just finished the other day that writes formatting info in a range of cells including a non-contiguous selection looks like...

function prettifySheet(FQFN,cats,yrs,heightOutMax)
  % a helper function to build a range address in Excel
  fnXLRange=@(r,c,w,h)strrep(compose('%s:%s',xlsAddr(r,c),xlsAddr(r+h-1,char(c+w-1))),'$','');
  ...
      
  % now set text alignment, etc....
  r=r-4; h=3; c=char(c-3); w=1;         % row, height, column, width of range to build
  rnge=fnXLRange(r,c,w,h);
  rnge=string(compose('%s,%s',rnge{:},strrep(xlsAddr(r+3,char(c+2)),'$','')));
  %disp(rnge), disp(' ')
  Excel_utils.hAlignRange(excel,rnge,'xlRight')
  Excel_utils.FormatCellFont(excel,rnge,[],[],[],true)
  ...

An example of what the above builds for the range expression is--

K>> r=3;c='B';w=3,h=3;          % row,column,width,height 
K>> rnge=fnXLRange(r,c,w,h)     % build the contiguous range expression
rnge =
  1×1 cell array
    {'B3:D5'}
%  add another non-contiguous location; this is just a cell but can be
%  another range as well...
K>> rnge=string(compose('%s,%s',rnge{:},strrep(xlsAddr(r+3,char(c+2)),'$','')));
K>> disp(rnge)
B3:D5,D6
K>> 

NB: the non-contiguous ranges are separated by a comma. The Excel_utils class is from the one @Image Analyst built and posted; I've added to it locally for a few specific things was interested in/needed somewhat differently than the original, but the base COM code remains. I don't have as extensive an amount of stuff to write so the speed issue hasn't been a problem and so haven't embedded the COM code but continued to use the class methods. However, there's no reason that code can't be brought in line -- it looks like

function hAlignRange(Excel, range, horizAlign)
  if ~isstring(range), range=string(range); end   % a cellstr array doesn't work for range
  merge=false;
  if ~isnumeric(horizAlign)
    if contains(horizAlign,'left','ignorecase',1)
      horizAlign=-4131;
    elseif contains(horizAlign,'right','ignorecase',1)
      horizAlign=-4152;
    elseif contains(horizAlign,'center','ignorecase',1)&~contains(horizAlign,'across','ignorecase',1)
      horizAlign=-4108;
    elseif contains(horizAlign,'general','ignorecase',1)
      horizAlign=1;
    elseif contains(horizAlign,'across','ignorecase',1)
      horizAlign=-4108;
      merge=true;
    else
      return
    end
  end
  try
    Excel.Range(range).Select;
    Excel.Selection.HorizontalAlignment = horizAlign;
    if merge, Excel.Selection.Merge, end
  catch ME
    errorMessage = sprintf('Error in function hAlignRange.\n\nError Message:\n%s', ME.message);
    fprintf('%s\n', errorMessage);
  end % from hAlignRange
  return;
end % of the hAlignRange() method.

You'll note that while the top-level code uses cellstr() operations in order to not rewrite my xlsAddr utility that was built before the introduction of the string() class, the Excel_utils functions/methods cast the range to a string() if they're not. The COM interface cannot handle a cellstr() range address -- only a string or a char() variable or literal char() string.

Also note that the above strips the "$" absolute addresses -- I've not tested extensively at the low-level COM interface level, but the MATLAB xlswrite fails with them and so I've kept them out for compatibility with old code. xlsAddr builds them in (I really should add a flag control input variable) so they can be used to build formulas that may need them to keep absolute references.

Also NB: that xlsAddr accepts either the letter or a numeric value for the column so one can do arithmetic on computing new columns. I sometimes use all numeric values, often will start with the initial letter as in the above example and then use the ability of MATLAB to silently coerce a char() variable to double to do the arithmetic and then back again...just whatever turns out to be more convenient.

Hope that helps...

[Bobby]

Thank you for the response. I may be misunderstanding what you're saying, but I get my problem when I run this line of code:

xlsWS.Range(asdf).Interior.Color=rgbGreen

If the Range argument is longer than 255 characters, I get an error. String, character, doesn't matter. Anything under that character limit works. If I can expand this limit somehow, so that the coloring happens in one line that's run, versus several loops (shown in my original question), then I would save a lot of time.

It looks like your code doesn't run into my character limit issue, so I'm not sure if this would help me?

Thanks!

[dpb]

I didn't find it in a MS document but indeed, the limit of a range address appears to be 255 characters. That seems incredibly low for current releases of Excel.

It would seem your alternative is to build a string as long as you can up to the limit and then iterate.

I see some indications that maybe one can use a UNION method that can accept some number of arguments, but "I know nuthink!" about that so would have to 'spearmint to see how to use it or if it would help.

All the more reason to not use Excel except as last resort... :)

My problems are all with the use of a "veritable plethora" of sequential workbooks that must process, but none of them individually are all that large so I've never run into such an issue...and never would because I'd simply not use Excel for such large files, meself. It's only doing the pro bono work for the local college foundation that has all their financials tied up into all these workbooks that I deal with Excel at all to try to bring some facilities to use those more effectively until we can get transitioned over to a new accounting package.

[Bobby]

I've also tried using Union but none of my solutions worked. I don't think you can actually use the Union command in Matlab =(

Thanks anyway!

[dpb]

If it is an Excel method, then it undoubtedly can be called from COM -- you may have to modify the VBA syntax some to be able to get the right syntax passed since MATLAB/COM don't have the VBA compiler to interpret syntax; you have to get it to the lowest common denominator manually. And, of course, that isn't directly documented other than by the explorer completion of objects/methods/properties that are visible at and to any given object. If it is a higher-level VBA construct, then it is possible there isn't a low-level interface exposed; at this point I've not researched it enough to know what it actually is.

[dpb]

Well, looks like it should be possible -- although it depends on how union is implemented. If it creates an object of the subject ranges, then it should let you combine whatever you want; if, otoh all it does is build a composite Range expression to pass, then it's going to have the same 255 character limit.

Searching the MS documentation has failed to find any reference to this limitation.

The VBA doc gives the following example is @ https://docs.microsoft.com/en-us/office/vba/api/excel.application.union and on the surface looks like it should work unless you do run into the problem above that all it does is build a combined string and not create a new object using the passed ranges.

[Bobby]

So I don't actually have much VBA experience, but from what I have tried it seems like my issue is with creating the variable, becaise this line doesn't work for me:

Union(Range("A1"), Range("A10")).Select

So I try to do something like this:

bigrange = xlsWS.Union(Range("asdf"), Range("asdfg"))

but I get this error: Undefined function 'Range' for input arguments of type 'string'. I also get the same error when I make it a char.

Not sure how I would go about this because I just don't have the VBA background to really solve this myself. But thanks for the replies!

[dpb]

The example in the MS documentation

Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(fullfile(FOLDER,'testunion.xlsx'))
Excel.Worksheets.Item("Sheet1").Activate
uRnge=Excel.Application.Union(Excel.Range("A1"),Excel.Range("A10"))
uRnge.Formula="=RAND()"
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(false)
delete(Excel),clear Excel

worked as advertised -- the above begins to illustrate what you have to do to simulate the VBA compiler translation to low-level commands that the COM engine can understand (really, instantiating references to the COM object you created instead of the MATLAB interpreter trying to read all as MATLAB variables/functions).

The reference to the 'Item' array in the Worksheets collection instead of trying to just write

Excel.Worksheets("Sheet1").Activate

is a hard-won lesson of trial and error and finding other code smarter folks than I had figured out...

I think it is illustrated the concept works--now whether it will be any faster or not, only experience and trying it out will determine.

The key is to activate the sheet you're working on, build a range object as the output from the Union method and then do your thing on that compound range.

The Q? will be whether it's any faster to make a call to Union with a whole bunch of arguments with individual references to the Range method over just calling each iteratively. The MATLAB code to build a set of string references will be fast, of course, the possible bottleneck will be in turning those into Excel range references/objects that the Union method expects...it doesn't know anything about just a cell address, unfortunately.

I presume but didn't test that you can build range expressions to pass to .Range() above that are also compound references, whether the overhead then is reduced or not again only testing will tell.

[dpb]

bigrange = xlsWS.Union(Range("asdf"), Range("asdfg"))

"asdf" and "asdfg" would have to be named ranges in the workbook for the above to work...and you would also need to reference xlsWS.Range so MATLAB doesn't interpret Range as an array or function reference. Remember you don't have VBA inbetween the keyboard and the COM object inside MATLAB to supply the context.

[Bobby]

I actually got it to work! You were right in questioning the time savings of this method. Before I implemented this, I had just completed another method that put my runtime to almost exactly the same as now (but not as consistent). Now, the bulk of my runtime is spent on this line:

color1 = xls.Application.Union(color1, xls.Range(rangeidx{i,1}(1:end-1)));

I have to iterate this for as many segments of 255 or less character strings created in the earlier loop to combine the range strings into a singlular variable. I know that Union can take up to 30 arguments, but I'm not sure right now how to apply this when I have about 10 different color variables, each with anywhere from 3 to 100 iterations needed. So I would need a way to create this large range variable regardless of how many arguments I can put on there.

I appreciate all your help! I know this is a separate question now, so I'll probably just post another if I can't figure anything out, but if you have any suggestions for this as well, I'd love to hear.

[dpb]

I can't really visualize what you're trying to describe from the verbal description and trying to decipher the code only leads to see what you've written which is, as you say, not necessarily optimal. :)

I agree posting another Q? that gives an actual example of what you're trying to do in a smallish manner would be the route most likely to lead to any more clever ideas.

What I'd probably try just to see if it is worth pursuing would be to create some trial cases of range strings and just some trial code to time the various forms of calling to get an idea of the overhead costs of the different approaches. This should give you an idea of whether any can beat the one-at-a-time loop construct or not, and if so, which of the ways is least overhead in building the union. The result, hopefully, would give you a direction to pursue for actual implementation.

The Q? I would ask, however, is the most basic one -- is it really that important to add all the color to such a large spreadsheet? Who is ever going to actually look at something that large by hand, anyway?

[dpb]

Overnight a thought that perhaps the way might be is it possible to use the conditional formatting feature instead of explicitly writing a color on a cell-by-cell basis? Then you could potentially just write a single formatting expression globally. I know "almost nothink!" about the more exotic usage facilities, but would guess it would be worth digging into just what can be done.

[Bobby]

Haha, good question. But yes, coloring all of these rows is necessary for my task.

So I haven't used conditional formatting before, and looking at the Microsoft help page makes it seem difficult for my application (or just because of my inexperience). The coloring scheme takes in to account two columns; the first being one of two options, the second being one of four. These determine the coloring of the background and the font vs just the background. The coloring index I use to sort out all the coloring formats is not in the Excel sheet, so I would probably have to rewrite the entire coloring section of my code, which is fine if I saw a clear path at my task. But like I said, my inexperience would require a lot of time researching the method itself.

Answer 2

[dpb]

I'll throw the last example code out as an Answer as well as Comment just for visibility -- and it may turn out useful for others as well.

Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(fullfile(FOLDER,'testunion.xlsx'))
Excel.Worksheets.Item("Sheet1").Activate
uRnge=Excel.Application.Union(Excel.Range("A1"),Excel.Range("A10"))
uRnge.Formula="=RAND()"
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(false)
delete(Excel),clear Excel

The example in the MS documentation worked as advertised -- the above begins to illustrate what you have to do to simulate the VBA compiler translation to low-level commands that the COM engine can understand (really, instantiating references to the COM object you created instead of the MATLAB interpreter trying to read all as MATLAB variables/functions).

The reference to the 'Item' array in the Worksheets collection instead of trying to just write

Excel.Worksheets("Sheet1").Activate

is a hard-won lesson of trial and error and finding other code smarter folks than I had figured out...

I think it is illustrated the concept works--now whether it will be any faster or not, only experience and trying it out will determine.

The key is to activate the sheet you're working on, build a range object as the output from the Union method and then do your thing on that compound range.

The Q? will be whether it's any faster to make a call to Union with a whole bunch of arguments with individual references to the Range method over just calling each iteratively. The MATLAB code to build a set of string references will be fast, of course, the possible bottleneck will be in turning those into Excel range references/objects that the Union method expects...it doesn't know anything about just a cell address, unfortunately.

I presume but didn't test that you can build range expressions to pass to .Range() above that are also compound references, whether the overhead then is reduced or not again only testing will tell.