Need to determine LastRow over the Whole Row

Question

I am not a programmer but have managed to cobble together great amounts of code that work on 4 pretty large projects (Yay for me!) I have tried numerous ways to find the Last Row. Some work for me some don't. I can find a few that give me the "actual" last row regardless of blanks in Column A (this is what I need). Yet I CANNOT for my life figure how to integrate that code with the way I am passing values from my array from one workbook to another. All of the code works "As Is" but I need to find a better way of searching the whole row (currently columns A:O) for the Last Row and then copying the data over. Column A maybe empty at times and to avoid the code from being overwritten, that "Last Row" needs to check the whole row. I am currently forcing a hidden cell (A7) with a "." as a forced placeholder. Any advice would be awesome.

 Option Explicit

 Public Sub SaveToLog15()

 Dim rng As Range, aCell As Range
 Dim MyAr() As Variant
 Dim n As Long, i As Long
 Dim LastRow As Long
 Dim NextCell As Range
 Dim Sheet2 As Worksheet

 Set Sheet2 = ActiveSheet

 Application.ScreenUpdating = False

  With Sheet2
    '  rng are the cells you want to read into the array.
    '  Cell A7 (".") is a needed "Forced Place Holder" for last row _
       determination
    '  A7 will go away once "better" LastRow can be added to this code
    Set rng = Worksheets("Main").Range("A7,D22,D19,D20,J22:J24,E23,D21,J25:J27,D62,D63,G51")
         '  counts number of cells in MyAr
    n = rng.Cells.Count
         '  Redimensions array for above range
    ReDim MyAr(1 To n)
         '  Sets start cell at 1 or "A"
    n = 1
         '  Loops through cells to add data to the array
    For Each aCell In rng.Cells
        MyAr(n) = aCell.Value
        n = n + 1
    Next aCell
  End With

   On Error Resume Next
   '   Opens "Test Log.xls"
   Workbooks.Open FileName:= _
   "S:\Test Folder\Test Log.xls"

    '  SUBROUTINE 1 "Disable Sheet Protection and Show All" REMOVED

   '  Finds last row on Tab "Tracking" based on Column "A"
   '  Last row determination DOES NOT go to next row if first _
      Column is blank
   '  Use A7 "." to always force Data to Col A
'**********************************************************************
'THIS WORKS FINE BUT DOES NOT RECOGNIZE THE POSSIBLE BLANK IN COL A.

   With Worksheets("Incoming Data")
   Set NextCell = Worksheets("Incoming Data").Cells _
   (Worksheets("Incoming Data").Rows.Count, "A").End(xlUp).Offset(1, 0)
   End With

' I need this code replaced by the following code or integrated into 
' this code snippet. I am lost on how to make that happen.
'***********************************************************************

'***********************************************************************
'THIS CODE FINDS THE "ACTUAL" LAST ROW AND THIS IS WHAT I'D LIKE TO USE
' I need to figure how to integrate this code block with the above
' Or maybe redo the whole thing. 

     LastRow = Cells.Find(What:="*", After:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
     MsgBox ("The Last Row Is: " & LastRow)

' I am not using this code in the program. It's just there to show
' what I need to use because it works. I need to make this code work
'WITH the above block. 
'***********************************************************************

    '  Sets the size of the new array and copies MyAr to it
    NextCell.Resize(1, UBound(MyAr)).Value = (MyAr)

    '  SUBROUTINE 2 "Add borders to cells in range" REMOVED
    '  SUBROUTINE 3 "Re-enable Sheet Protection" REMOVED

       ActiveWorkbook.Save
       'ActiveWindow.Close
       Application.ScreenUpdating = True

    MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _
    & "'Test Log.xls'", vbInformation, "Log Save Confirmation"

 End Sub

You have the code that `Finds the actual last row`. What problem are you having? Btw, You'll need to explicitly reference the objects your working on to the worksheet where you're doing your search. Like what you did in setting `NextCell`. — L42, Jan 28 '15 at 21:53
[This](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) is a good reference as well. — TheEngineer, Jan 28 '15 at 22:08

score 1 · Answer 1 · answered Jan 28 '15 at 22:01

This is a common problem with "jagged" data like:

enter image description here

Clearly here column B has that last row.
Here is one way to get that overall Last row by looping over the four candidate columns:

Sub RealLast()
    Dim m As Long
    m = 0
    For i = 1 To 4
      candidate = Cells(Rows.Count, i).End(xlUp).Row
      If candidate > m Then m = candidate
    Next i
    MsgBox m
End Sub

:

score 1 · Answer 2 · answered Jan 28 '15 at 22:05

Find works best for most situations, below is the function i use that takes sheet ref as input and returns row number as type Long

Dim lLastRow As Long
lLastRow = LastUsedRow(shName)




  Private Function LastUsedRow(sh As Worksheet) As Long
        LastUsedRow = sh.Cells.Find(What:="*", After:=sh.Cells.Cells(1), _ 
        LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious, MatchCase:=False).Row
    End Function

tpkaplan · Answer 3 · 2015-01-28T22:27:13.243

The simplest thing might be to use the specialcells method, as in range.specialcells(xllastcell). This returns the cell whose row number is the last row used anywhere in the spreadsheet, and whose column is the last column used anywhere in the worksheet. (I don't think it matters what "range" you specify; the result is always the last cell on the worksheet.)

So if you have data in cells B30 and X5 and nowhere else, cells.specialcells(xllastcell) will point to cell X30 (and range("A1").specialcells(xlastcell) will also point to cell X30).

Instead of:

LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
MsgBox ("The Last Row Is: " & LastRow)

use this:

LastRow = cells.specialcells(xllastcell).row
MsgBox ("The Last Row Is: " & LastRow)

the second code bit is JUST to show the code I need to use.. I can't figure how to get it to replace the code I do have, which is kludged by the blank A column. This is the part I need to change using the second code part: Set NextCell = Worksheets("Incoming Data").Cells _ (Worksheets("Incoming Data").Rows.Count, "A").End(xlUp).Offset(1, 0) — Valhalla_33, Jan 29 '15 at 03:28

Valhalla_33 · Accepted Answer · 2015-05-21T14:28:48.490

After 35 attempts this is the code that I was able to hack into my original:

 '  Used to determine LastRow, LastColumn, LastCell, NextCell
 Dim LastRow As Long
 Dim LastColumn As Integer
 Dim LastCell As Range, NextCell As Range

    With Worksheets("Tracking")
      '  Find LastRow. Works Best. 1st and last cells can be empty
         If WorksheetFunction.CountA(Cells) > 0 Then
         'Search for any entry, by searching backwards by Rows.
              LastRow = Cells.Find(What:="*", After:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
         'Search for any entry, by searching backwards by Columns.
              LastColumn = Cells.Find(What:="*", After:=[A1], _
                  SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious).Column
           'MsgBox "Last Cell" & vbCrLf & vbCrLf & Cells(LastRow, LastColumn).Address
           'MsgBox "The Last Row is: " & vbCrLf & vbCrLf & LastRow
           'MsgBox "The Last Column is: " & vbCrLf & vbCrLf & LastColumn
         End If
       ' Number of columns based on actual size of log range NOT MyAr(n)
       Set NextCell = Worksheets("Tracking").Cells(LastRow + 1, (LastColumn - 10))
    End With

This finds the "Real" Last Row and column and ignores any empty cells in Column A or J which seem to affect some of the LastRow snippets. I needed to make it ROWS instead of ROW and HAD the add the Offset portion as well. (-10) puts me back to Column "A" for my sheet and now I have removed Column "A" {the forced Place Holder "."} and have "Real" data there now. YAY for the "Hacking Code Cobbler".

Glad they pay me at work to learn this stuff. :) Solved this a while back. Just now got to update this post.

Need to determine LastRow over the Whole Row

4 Answers4