How Find last row in excel

Hi, I have three pages the data is in the second sheet.

In the second Sheet I would like to know which cell is the last data in column B.

from 'B2' are just numbers

Thanks

Answer 1

[Image Analyst]

If you're comfortable with using ActiveX, here is function:

%-----------------------------------------------------------------------------
% Returns the next empty cell in column after row 1.  Basically it puts the active cell in row 1
% and types control-(down arrow) to put you in the last row.  Then it add 1 to get to the next available row.
function nextRow = GoToNextRowInColumn(Excel, column)
  try
    % Make a reference to the very last cell in this column.
    cellReference = sprintf('%s1048576', column);
    Excel.Range(cellReference).Select;
    currentCell = Excel.Selection;
    bottomCell = currentCell.End(3); % Control-up arrow.  We should be in row 1 now.
    % Well we're kind of in that row but not really until we select it.
    bottomRow = bottomCell.Row;
    cellReference = sprintf('%s%d', column, bottomRow);
    Excel.Range(cellReference).Select;
    bottomCell = Excel.Selection;
    bottomRow = bottomCell.Row;  % This should be 1
    % If this cell is empty, then it's the next row.
    % If this cell has something in it, then the next row is one row below it.
    cellContents = Excel.ActiveCell.Value;  % Get cell contents - the value (number of string that's in it).
    % If the cell is empty, cellContents will be a NaN.
    if isnan(cellContents)
  % Row 1 is empty.  Next row should be 1.
  nextRow = bottomRow;  % Don't add 1 since it was empty (the top row already).
    else
  % Row 1 is not empty.  Next row should be row 2.
  nextRow = bottomRow + 1;  % Will add 1 to get row 1 as the next row.
    end
  catch ME
    errorMessage = sprintf('Error in function GoToNextRowInColumn.\n\nError Message:\n%s', ME.message);
    fprintf('%s\n', errorMessage);
    WarnUser(errorMessage);
  end
  return; % from LeftAlignSheet
end % of GoToNextRowInColumn

[Aitor Vitoria]

Hi, Thanks for your answer, but I'm new with matlab and not get it to work.

Sorry but what I have to put in Excel and Column??

     - prueba.xlsx (Workbook)
     - Datos (sheet2)

if true
  ex = actxserver('excel.application');
  ex.Workbooks.Open(fullfile(pwd,'Prueba.xlsx'));

    exSheets = ex.ActiveWorkbook.Sheets;
    Datos = exSheets.get('Item', 2);
    Datos.Activate;

    Excel = ?? 
    column = ??
    GoToNextRowInColumn(Excel, column)

end

Thanks

[dpb]

I try to avoid using ActiveX like the plague, but since IA hasn't been back (yet), I'll presume to take a guess that

Excel = ex; 
column = 'B';

[Image Analyst]

That's correct, so it would be called like this:

GoToNextRowInColumn(Excel, 'B');

I'd recommend you use Excel rather than ex in your code - it's so much more descriptive of what it really represents than the cryptic "ex".

Answer 2

[dpb]

Not determinable unless ActiveX/COM interaction is an Excel method. In "just Matlab" best you could do would be

data=xlsread('Workbook.xls','Sheet2','B:B');  % read the column of interest
row=length(data)+1;                           % last row is length+1

[Image Analyst]

I think you'd have to do that on the raw cell array if there is a mixture of test and numbers:

[caNumbers, caText, caRaw] = xlsread('Workbook.xls','Sheet2','B:B');

[dpb]

True, but that wasn't the example given that "from 'B2' are just numbers"

[Image Analyst]

Actually the raw cell array returns the whole sheet and cells with nothing in them are returned as NAN for some reason - (weird, I didn't know that). So you don't want to use the raw. However for the sample given with text in the first row (aaa,bbb,ccc,ddd) and numbers in rows 22-11, we'd want the next row to be 12 - assuming that would be the next empty row. But since the numbers and text aren't synced up as far as location goes, the numbers cell array is 10 rows long and the text one is one row. So it looks like you'd have to add the text rows (1) to the numbers rows (11) and then add 1.

[caNumbers, caText, caRaw] = xlsread('book1.xlsx','Sheet2','B:B');
[rowsn, columnsn] = size(caNumbers) % 10 rows by 1 column
[rowst, columnst] = size(caText) % 1 by 1
[rowsr, columnsr] = size(caRaw) % 1048576 by 1
nextEmptyRow = rowsn + rowst + 1

[dpb]

Actually, the request was "I would like to know which cell is the last data in column B." so the answer is row 11, not 12.

For a more general solution, "trudat" -- but again that wasn't what was given by OP as the objective. :)

As far as the RAW output, I believe it returns the extent of the rectangular area encompassing all non-empty cells on the sheet; if there are empty rows/columns above/left of the data those won't be returned.

I think there is a difference internally in Excel as to whether a cell has at one time contained data or not that may influence this but that is conjecture, not necessarily fact; it may be that fignewton of my recollection is based on a mis-interpreted result from some time in the past.

RAW basically is a pattern of the data area within the sheet from which one can, in theory "do whatever" with; albeit it's not necessarily a 1:1 mapping as to the location of a cell in the Matlab cell array and the Excel spreadsheet cell.

For example, I have a very small test spreadsheet which has two rows and three columns of data in B3:B4,D3:E4. RAW returns a 2x4 cell with the NaN placeholder for C2:C3 locations, but rows 1:2 and column A are ignored.

Hence, you can't determine the answer to OP's question unequivocally unless you know (as was given) the header row is in the first row or, equivalently, how many blank rows there are preceding the data.

I presume the ActiveX solution can be made to work but it's such a nightmare of syntax I make no effort and that becomes an OT question anyway as it is then entirely Excel syntax, not Matlab and the poster will be better served to ask Excel whizards instead.

[Image Analyst]

To get the last used row you'd do

lastUsedRow = rowsn + rowst;

I created a new blank spreadsheet and put in 4 columns and 11 rows, and for some reason that I don't understand the raw cell array was not 11 rows tall, it was 1,048,576 tall even though I never put anything down there. I've attached my workbook. I'm not sure why it works differently for you. I have MATLAB R2016a and Excel 2013. If I, in Excel, type control-end, it goes to the bottom right corner of the data that is actually there, as you'd expect. I don't know why xlsread() gets a different range but they did a lot of changes to xlsread() in R2015b or R2016a.

The ActiveX way that I can think of is to do what I did, but just don't add 1 to the row, which puts it a row below.

A nifty trick for figuring out ActiveX code is to just record a macro in Excel, then edit the macro. Then transfer the macro code to your m-file.

[dpb]

Well, there's still something wrong with downloading files from the links here in Answers with Firefox; it won't save anything except as text so I can't get to your file, sorry. I don't know if it something TMW is doing that is, like, MS-specific for Explorer or if Firefox is broke but it's been this way since, like forever, now (although at the very firstest, I don't recall it being a problem). (+)

It probably wouldn't prove anything anyway as I'm still on this old machine with R2012b and Office 2000.

I've seen things like that with this combination, too, on occasion, however. I've no klew what causes it (and doubt that MS would, either, frankly).

As for recording macros; yeah, that is essentially the only way one can write code for Excel unless one wants to make it a career. I'll leave that to those who want to and will stick to the parts that are, at least, Matlab syntax. :)

(+) If you want, you can send the file via the contact link; I think it's visible, IA...