VBA HELP - Find Last Row of first range and not the range below - Excel 2013

[magnum5az]

Hi Guys,

I have a code that updates my named range "GrandTotal" which stores my Subtotal row from a Main Sheet. The Main sheet has two ranges of data, the main range A6:H144 which updates each day, and a lower data range A146:H152.

I am trying to have the code for Last row only look in the first range, similar to Starting from cell A6 and doing a CTRL+Shift+Down but not able to get it right.

Here is my code

Sub UpdateSub()

Dim LR As Long
Dim ws As Worksheet

Set ws = Sheets("NOI - Combined Property")

LR = ws.Range("A" & Rows.Count).End(xlDown).Row

Application.ScreenUpdating = False

ws.Activate
    With ActiveWorkbook.Names("Grandtotal")
        .Name = "Grandtotal"
        .RefersToR1C1 = "='NOI - Combined Property'!R" & LR & ""
        .Comment = ""
    
    End With

End Sub

 

[onlyadrafter]

Hello,

isn;t it just a case of changing

LR = ws.Range("A" & Rows.Count).End(xlDown).Row

to

LR = ws.Range("A6").End(xlDown).Row

or am i missing something? i.e. blank cells?

 

[JoeMo]

Assuming there are no empty cells between A6 and the "last" row of your upper range, try:

LR = ws.Range("A6").End(xlDown).Row

 

[magnum5az]

Can't beleive I missed that. The revision worked great.

Hello,

isn;t it just a case of changing

LR = ws.Range("A" & Rows.Count).End(xlDown).Row

to

LR = ws.Range("A6").End(xlDown).Row

or am i missing something? i.e. blank cells?

 

[magnum5az]

Thank you for chiming in. The revision worked out.

Assuming there are no empty cells between A6 and the "last" row of your upper range, try:

LR = ws.Range("A6").End(xlDown).Row