Get Last non Empty Cell of Excel Column Programatically

Question

I have excel sheets that always look like below

A |  B  |  C
  |     |    
  |     |  something here maybe 
1 |  x  |  k
2 |  y  |  l
3 |  z  |  n
  |  j  |  
  |  j  |  
   ...
  |     |

I want to get the value of the last non empty cell in A column. A column always starts at A6 and goes for a while and after than it is empty but column B continues for a while. This has the effect that if I use the code below:

sh = app.Workbooks.get_Item("Workbook1.xlsx").Worksheets.get_Item("Sheet1");
fullRow = sh.Rows.Count;
lastRow = sh.Cells[fullRow, 1].End(Excel.XlDirection.xlUp).Row;

I get as last row the last row that has B non empty. Which might be even 50 rows below the place where A starts being null. Is there a way to do it with C# without iterating over all values?

EDIT

It turns out that Column A is based on column C so I should check for C. As it turns out C is yielding the same result with A maybe because of the function. Luckily there is a column D that does not have the same issue so if I use the code above changing 1 for 4 it works likes a charm.

Is sh.Cells zero-based, i.e., can you do sh.Cells[fullRow, 0]? I don't think so, but worth checking. — Doug Glancy
– Doug Glancy, Commented Aug 12, 2013 at 16:42
your code is ok for what you need. The only possibility I could think of, which gives you this result, is if you have any formulas in 'empty' cells in column A. These could be formulas which return empty string, like ="". — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 12, 2013 at 17:06
@DougGlancy I have already tired that and it throws an exception. — idipous
– idipous, Commented Aug 12, 2013 at 18:04
@KazJaw I will check for it but I a pretty sure it does have any formulas. — idipous
– idipous, Commented Aug 12, 2013 at 18:06
@idipous, in other words, try to check it manually in your excel file, with ctrl+arrows, where movement will stop. then analyse the content of cells. — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 12, 2013 at 18:14

Community · Accepted Answer · 2017-05-23 11:49:50Z

1

EDIT: Sorry, I initially misunderstood the question, thanks KekuSemau for clearing that up!

Try:

sh.Range["A1", sh.Cells[1,sh.Rows.Count]].End(Excel.XlDirection.xlDown).Value;

Also, if you know there will be no empty cells in the column you could use CountA:

var usedRows = xlApp.WorksheetFunction.CountA(xlWorkSheet.Columns[1]);

Source

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 12, 2013 at 17:12

Seth Moore

3,5452 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KekuSemau Over a year ago

Sorry for downvote, but this is really counterproductive. The OP already uses Range.End function which is the programmatic equivalent of pressing ALT-Cursorkeys. No need to tamper with sending keys and looking where Selection walks off to.

KekuSemau Over a year ago

Yes, sorry, I meant CTRL-Arrow.

idipous Over a year ago

The problem is that I am not in A1 but A6 always since that's where the column starts with the previous 5 cells being of no interest to me at that particular moment. could I use something like sheet.get_Range("A6",????).End; ?

Collectives™ on Stack Overflow

Get Last non Empty Cell of Excel Column Programatically

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related