Finding the last nonblank cell in a column in VBA or VSTO

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Folks,

In VBA I have frequently used the formula:

last_row = ActiveWorkbook.Sheets("Sheet1").Range("A65534").End(xlUp).Row

to find the last cell with data in it in column A of Sheet1.

In Visual Studio Tools for Office (C#) the equivalent expression I have just
discovered is:

Sheet1.get_Range("A65534", Type.Missing).get_End(Excel.XlDirection.xlUp).Row
 
Your method would not work if the last cell used was on row 65534, 65535, or
65536. Even if you corrected your typo (should have said 65536), if the data
extended to row 65536, you'd end either on Row 1 or on the last occurrence
of a break in the data, using End(xlUP). And what if there is no data in Col
A ... or what if the data in colums other than A extended below rows which
Col A's data held? There are a lot of other ways to get last row. The
quickest I know is:
Activesheet.UsedRange.SpecialCells(xlcelltypelastcell).Row
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to group empty cells in a column 3
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Find all in vba 5
Row and column count last occupied cells in range 2
Select all rows till last row with value in Column A and CopyPaste 7
Last cell in new formula 6
copy/paste in the next column 4
Setting range to last occupied cell 3

Back
Top