Port Excel VBA "ActiveCel​l.SpecialC​ells(xlLas​tCell).Sel​ect" (ctrl-end) and "Range(Selection, ActiveCell​.SpecialCe​lls(xlLast​Cell)).Sel​ect" (ctrl-shift-end) to MATLAB using ActiveX Connection

I'm try to port over two VBA commands. The following example assumes that you fill in file_directory, file_name, and sheet_name with strings for an actual Excel File.

%Open an ActiveX connection to Excel
try
    h = actxGetRunningServer('excel.application');
catch
    try
        h = actxserver('excel.application');
    catch
        disp('MATLAB was unable to obtain an ActiveX connection to Excel.')
        return
    end
end

%Open a workbook and select sheet
wb=h.WorkBooks.Open(fullfile(file_directory,file_name)); 
wbs=h.ActiveWorkBook.Sheets;
wbs.Item(sheet_name).Select;

%Select cell A1
getA1 = h.Activesheet.get('Range','A1');
selA1 = getA1.Select;

Now that I am at cell A1, I want to be able to perform "ctrl-end" and "ctrl-shift-end" in Excel. The recorded VBA from a macro for these two keyboard commands are "ActiveCell.SpecialCells(xlLastCell).Select" and "Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select", respectively.

I have tried a few ideas to no avail such as

h.Selection.SpecialCells(1)

and

h.Selection.SpecialCells.Item('xlLastCell')

Any help would be greatly appreciated. Thanks.

Answer 1

[Shawn]

I was able to figure it out. Once I have an active sheet selected, I can then do

%get cell A1
getA1 = h.Activesheet.get('Range','A1');
%select cell A1
selA1 = getA1.Select;
%set cell A1 as the current cell
currentcell = h.Selection;

%apply ctrl-end and select that cell
currentcell.SpecialCells(11).Select % ctrl-end is special cells item 11
%get the address from the selected cell
selectedcell = strrep(h.Selection.Address,'$','');

%get the constructed range from ctrl-end to immitate ctrl-shift-end 
getctrlshiftend = h.Activesheet.get('Range',['A1:' selectedcell]);
%select the cells as you would with ctrl-shift-end
selctrlshiftend = getctrlshiftend.Select;

[Shawn]

The effect could also be constructed from

rownum = h.Activesheet.UsedRange.Rows.Count;
colnum = h.Activesheet.UsedRange.Columns.Count;

if you use a column number to column letter converter such as xlscol on the file exchange.