Last Row

[billandrew]

Good afternoon

Could someone provide an explanation why the below endrow variable is not locating the last row of data in it's current position in the script. It stops format before the last row. This maybe an easy one, I am unable to see it.

Sub FormatRange()
Dim endrow As Long
endrow = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
 
With Range("I1:M4")
.VerticalAlignment = xlBottom
.WrapText = True
.HorizontalAlignment = xlCenter
End With
 
Cells(1, 1).Font.Size = 42
Cells(1, 1).Font.Bold = True
 
With Range("A2", Range("M" & Rows.Count).End(xlUp))
.Font.Size = 30
.Font.Name = "Calibri"
End With
 
Range(Cells(6, 1), Cells(6, 13)).Font.Size = 28
 
With Range("I7", Range("I" & endrow))
.NumberFormat = "#,##0.00"
.Font.Size = 30
End With

 

[Richard U]

Ron De Bruin wrote some code that is fantastic. Best way to get the last row, column, or cell in a range.

Option Explicit

 Public Function GetLast(choice As Long, rng As Range)
    'Ron de Bruin, 5 May 2008
    ' 1 = GetLast row
    ' 2 = GetLast column
    ' 3 = GetLast cell
        Dim lrw As Long
        Dim lcol As Long
    
        Select Case choice
    
        Case 1:
            On Error Resume Next
            GetLast = rng.Find(What:="*", _
                            After:=rng.Cells(1), _
                            lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
            On Error GoTo 0
    
        Case 2:
            On Error Resume Next
            GetLast = rng.Find(What:="*", _
                            After:=rng.Cells(1), _
                            lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0
    
        Case 3:
            On Error Resume Next
            lrw = rng.Find(What:="*", _
                           After:=rng.Cells(1), _
                           lookat:=xlPart, _
                           LookIn:=xlFormulas, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False).Row
            On Error GoTo 0
    
            On Error Resume Next
            lcol = rng.Find(What:="*", _
                            After:=rng.Cells(1), _
                            lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0
    
            On Error Resume Next
            GetLast = rng.Parent.Cells(lrw, lcol).Address(False, False)
            If Err.Number > 0 Then
                GetLast = rng.Cells(1).Address(False, False)
                Err.Clear
            End If
            On Error GoTo 0
    
        End Select
    End Function

use that instead. it's more reliable

 

[billandrew]

Thank you for the information. This does not entirely explain my question.

 

[Michael M]

I'm guessing last row in "M" isn't as far dowwn as lastrow in "H"

With Range("A2", Range("M" & Rows.Count).End(xlUp))

So why not use

With Range("A2", Range("M" & endrow))

 

[billandrew]

No That's not it Michael, all columns are of equal length. I'm not sure here??

 

[billandrew]

If I relocate the endrow from the top

endrow = Range("H" & Rows.Count).End(xlUp).Row

to above this line then I seem to have no problem?

With Range("I7", Range("I" & endrow))
.NumberFormat = "#,##0.00"
.Font.Size = 30
End With

 

[MARK858]

If I relocate the endrow from the top

endrow = Range("H" & Rows.Count).End(xlUp).Row

to above this line then I seem to have no problem?

With Range("I7", Range("I" & endrow))
.NumberFormat = "#,##0.00"
.Font.Size = 30
End With

That's because in the original code you have the code endrow code above where you make Sheet1 the active sheet so it is using the row number of whatever sheet is active at the time.

endrow = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Sheets("Sheet1").Activate

 

[MARK858]

Ron De Bruin wrote some code that is fantastic. Best way to get the last row, column, or cell in a range.

....

use that instead. it's more reliable

Find is no more reliable, it is just a different method, where you have more options for setting the parameters for instance with the code you posted it will find cells with formulas that return "" (as will the xlUp method).

One of the differences is with the Find method you have the option to ignore cells returning "" whereas with the xlUp method you don't have the option.

There are other benefits to the Find method such as you can you can use Find on the entire sheet or multiple columns which you can't with xlUp, but still both methods are reliable (it just depends what your data looks like and what you want to do with it).

 

[billandrew]

Thank you appreciate both explanations