Find Last Cell after 2 blanks cells - working down

[mbpress01]

First thanks in advance for any advice.

I have a reviewed a lot of examples that selects the last row working from bottom up. The main issue I have is that I have a column of data that starts at row 20 and I need to determine the last cell when the loop hits the second blank row working down from Row 20 since there is information after the second blank row and to make it more difficult there is a 1 blank row in the column before the next two blank rows (which is the row i need).

I tried the following:

Sub testlr()
'
'Dim Lastcella As Long
'Dim SecondLastcella As Long
'
'With Sheets("REG")
'
'Lastcella = .Cells(.Rows.Count, "A").End(xlDown).Row + 20
'
'Debug.Print Lastcella
'
'SecondLastcella = Lastcella - 1
'Do While .Cells(SecondLastcella, "A") = ""
' SecondLastcella = SecondLastcella - 1
'Loop
'
'End With
'
'Debug.Print Lastcella
'
'End Sub

This gives me the last row in the worksheet so its not working right and I am not sure how to proceed.

Thanks for all on this board with their advice. It is greatly appreciated.

 

[veryamusing]

Lastcella = .Cells(.Rows.Count, "A").End(xlDown).Row + 20

Should be

Lastcella = .Cells(.Rows.Count, "A").End(xlUp).Row + 20

I will keep thinking about the other part of your question.

 

[mbpress01]

Hi veryamusing and thanks. I figured out the following and it works great. It is rudimentary but "vba newbie proof". Some further questions. Now that I have the lastrow needed, how do I use that to assign a range name from say cell A20. What I tried was below but it didn't work and I got an error for the debug.print. My main objective was to first find the blank row if there are two blank rows in a row working from the top down. Once I have that (which works), use that row in a newly created range, which I can't get to work.

Sub Name_LastRw()

Dim wksRegSupp As Worksheet
Dim RegSuppLRw As Variant
Dim RegSuppRngLRw As Variant

Set wksRegSupp = Workbooks("Exposure_Rebuild.xlsm").Worksheets("REG")
wksRegSupp.Activate

'ActiveWorkbook.Names("RegSuppLRw").Delete

Range("A20").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
Debug.Print ActiveCell.Address
Selection.Name = "RegSuppLRw" 'syntax for selection.name need quotes for the defined name range
'wksRegSupp.Names.Add Name:="RegSuppRngLrw", RefersTo:="A21" & ":" & "regsupplrw" this did not work - I don't see the range name added!!!!!
'Debug.Print RegSuppRngLRw.Address - this failed - i got object required error.  


End Sub

Any help much appreciated

Thx

 

[veryamusing]

Try this...

Sub makeNamedRange()
    With Range("A20")
        Workbooks("Exposure_Rebuild.xlsm").Worksheets("REG").names.Add "RegSuppLRw", .Resize((.End(xlDown).Row - .Row) + 1).Address
    End With
End Sub

I think it will work for your purposes, but I may still misunderstand the "two blank rows" part.