Finding the last cell in an excel sheet?

Question

I'm using vb6 and copying data to an excel sheet. What I need to do is append data to an existing sheet. I am at the point where I need to find the index number of the last cell in the sheet (there's only one sheet.) I've tried a couple things like oBook.range.cells.specialcells(xllastcell) but to no avail. What I have right now to open the worksheet I want is this

If Not Dir(strPath) = "" Then
Set oExcel = CreateObject("Excel.application")
Set oBook = oExcel.workbooks.open(strPath)

but I have no idea what to do from here to find the last cell in the workbook.

Let me know if the answer works, I mis-read vb6 as vba so it may or may not work. — Tanner, Jul 07 '14 at 14:32
It would help if you posted what was the problem with what you tried (to no avail). — sancho.s ReinstateMonicaCellio, Dec 24 '14 at 11:42
Did you find any answer useful? Please remember to upvote and/or accept according to what you find. Such feedback is essential for SO. — sancho.s ReinstateMonicaCellio, Jun 11 '15 at 07:52

score 0 · Answer 1 · answered Jul 07 '14 at 14:30

0

There are several ways to do this. My favorite way is to do:

count = Application.CountA(Range("A:A")) 'you must use whatever column will give an accurate count

"count" will then be the last row containing data.

answered Jul 07 '14 at 14:30

Tanner

548
8
20

Ive seent his around but i think its exclusively vba, i tried it and vb6 did not recognize `range("A:A")` specifically "Range" – NickHallick Jul 07 '14 at 15:02

NickHallick · Accepted Answer · 2017-07-04T15:53:14.293

Upon scouring the internet for the answer I found a line of code that worked. I had to change some of the values such as xlbyrows to "1" and xlprevious to "2" because vb6 dows not recognize the terms but can use the number constants.

this was the original line of code

LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

this is what I changed it to that worked with my code

iCell = oExcel.cells.find("*", searchorder:=1, searchdirection:=2).row

score 0 · Answer 3 · edited May 23 '17 at 11:43

Info from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It deals with VBA, but it may help you here. It is an old reference, but in this respect valid.

Look under Last Cell. In particular,

"The last cell is considered to be the intersection of the highest numbered row in the worksheet that contains information and the highest numbered column in the worksheet that contains information. Excel also includes cells that have contained information during the current session, even if you have deleted that information. The last cell is not reset until you save the worksheet.

Excel considers formatted cells and unlocked cells to contain information. As a result, you will often find the last cell well beyond the region containing data, especially if the workbook has been imported from another spreadsheet application, such as Lotus 1-2-3. If you want to consider only cells that contain data in the form of numbers, text, and formulas, you can use the following code:

Sub GetRealLastCell()
  Dim lRealLastRow As Long
  Dim lRealLastColumn As Long
  Range("A1").Select
  On Error Resume Next
  lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlPrevious).Row
  lRealLastColumn = Cells.Find ("*",Range("A1"), xlFormulas, , _
                                    xlByColumns, xlPrevious).Column
  Cells(lRealLastRow, lRealLastColumn).Select
End Sub

"

Finding the last cell in an excel sheet?

3 Answers3

Linked