MATLAB: 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

excelvba

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.

Best Answer

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;
Related Question