How to find the last row in a dynamic sized section of rows in VBA?

Question

I have a spreadsheet that has a number of sections with a random number of rows for each one.

How can you find the last row for one of the section?

Example:

-------------------------------------------------------
ROW 1
-------------------------------------------------------
ROW 2
-------------------------------------------------------
ROW 3
-------------------------------------------------------
ROW 4
-------------------------------------------------------
LAST ROW <-- Select last row

However this can different for each section where the number of rows is different:

-------------------------------------------------------
ROW 1
-------------------------------------------------------
ROW 2
-------------------------------------------------------
LAST ROW <-- Select last row

This is the code I have already:

Sub AddNewAllocToSpendLine(sectionHeading As String, Optional sSheetName As String = c_Alloc2SpendSheetName)

    Worksheets(sSheetName).Activate

    'get the section heading position
    Set c = Worksheets(sSheetName).Range("A:A").Find(sectionHeading, LookIn:=xlValues, LookAt:=xlWhole)

    Debug.Print c

    Dim addrow As String
    Dim lRow As Long

    addrow = c.Row + 1

    If addrow <> "" And IsNumeric(addrow) = True Then

        Rows(addrow).Insert shift:=xlDown

    Else
        MsgBox ("enter only row number")
    End If

End Sub

`lastRow = worksheets("Sheet1").cells(worksheets("Sheet1").rows.count, 1).end(xlup).row` — Matt Cremeens, Jun 29 '16 at 13:54
@newguy sections of rows that are different from one another with different headings e.g. Education, Travel etc. — user3565164, Jun 29 '16 at 13:56
Also, see [this SO thread](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — BruceWayne, Jun 29 '16 at 13:59
Its basically finding the last row in the sheet, however I want it to select just the last row for that particular section. — user3565164, Jun 29 '16 at 14:01
For each section, there will be a button to do this - the section is passed in for each section — user3565164, Jun 29 '16 at 14:02

Brian · Answer 1 · 2016-06-29T14:09:57.407

0

If they are tables you could do this:

Dim r as Long
With ActiveSheet.ListObjects("Table1")
    r = .ListRows(.ListRows.Count).Range.Row
End With

But if you simply want to add another row at the end:

ActiveSheet.ListObjects("Table1").ListRows.Add

Or add before a particular row:

ActiveSheet.ListObjects("Table1").ListRows(3).Add
'You can change the '3' to a variable, of course

edited Jun 29 '16 at 14:09

answered Jun 29 '16 at 14:01

Brian

2,078
1
15
28

Brilliant I try that - tables makes sense. Thanks! – user3565164 Jun 29 '16 at 14:03
@user3565164 No problem! If it worked for you would you check this as an acceptable answer? – Brian Jun 29 '16 at 14:39

score 0 · Answer 2 · answered Jun 29 '16 at 14:43

Try this

Function FindSectionLastRow(rng As Range, header As String)
    Dim f As Found

    Set f = rng.Find(what:=header, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not f Is Nothing Then FindSectionLastRow = f.End(xlDown).Row
End Function

You could use in your main Sub as follows:

Sub AddNewAllocToSpendLine(sectionHeading As String, Optional sSheetName As String = c_Alloc2SpendSheetName)

    Dim sectionLastRow As Long 

    ' your code

    SectionLastRow  = FindSectionLastRow(Worksheets(sSheetName).Columns(1), sectionHeading)


    ' more code to use 'SectionLastRow'

End Sub

How to find the last row in a dynamic sized section of rows in VBA?

2 Answers2