How to find the first empty row of a .xlsx-file?
26 views (last 30 days)
Show older comments
I have a script in which I use writetable() to write data to a row in an excel spreadsheet. I need to get the row number of the next empty row in the file, but I haven't found a solution on this site.
Kind regards
Mikkel
1 Comment
dpb
on 3 Dec 2018
That's an Excel Q? more than Matlab; one syntax that works as macro is
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
Use COM to implement via ML.
Alternatively, read the data and find the size of the array and use that or keep a running counter and define a location in the spreadsheet that stores the information so can just retrieve it.
There is no magic query built into the ML interface, however.
Accepted Answer
Guillaume
on 3 Dec 2018
Edited: Guillaume
on 3 Dec 2018
So, in matlab, the unusedRow = Cells.SpecialCells(xlCellTypeLastCell) that dpb mentions, would be implemented as:
function lastrow = findLastRow(excelfile, sheetname)
%find last used row of an exel workbook
%excelfile: FULL PATH of excel workbook. Required, char vector or scalar string
%sheetname: sheet name or number. optional (default is 1st sheet). char vector, scalar string or scalar numeric
%TODO: add input validation
if nargin < 2
sheetname = 1;
end
excel = actxserver('Excel.Application'); %start excel
cleanup = onCleanup(@() excel.Quit); %make sure to close excel even if an error occurs. Will also close the workbook if it is open since it never gets modified
workbook = excel.Workbooks.Open(excelfile); %open workbook
worksheet = workbook.Worksheets.Item(sheetname); %get worksheet
lastrow = worksheet.Cells.SpecialCells('xlCellTypeLastCell').Row; %get last used row
workbook.Close;
end
Code untested. If there are bugs in the code, blame me. If Cells.SpecialCells(xlCellTypeLastCell) doesn't work, blame dpb.
10 Comments
Ajinkya Bankar
on 2 Nov 2019
Edited: Ajinkya Bankar
on 2 Nov 2019
Hello Sir, I am trying to use this function to find last row in excel file but I am getting error as shown in the screenshot. I do have A.xslx file in the same directory. Can you please help to find the error? Thank you.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/246138/image.png)
Guillaume
on 2 Nov 2019
You have to give the full path of the file. It's excel opening the file and excel doesn't know what matlab's current directory is.
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!