CODEWITHSUNDEEP
exceldynamicexcel-2010date-rangevba
Jeremy Scott
Jeremy Scott

Reputation: 177

Adding Next Month Programmatically (VBA) to a Dynamic Range Table in Excel

I have a dynamic ranged table [Defined Table Name = MainTable]. I also have code which allows me to find the last cell in DateRange (Defined Name Range for Column A). 

Sub Last_Row_Range()
Dim nextMonth As Range
Set nextMonth = Sheets("MainTable").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
nextMonth.Select
End Sub

However, here is what I can't figure out. I want the user to be able to press a button (btnNextMonth), which automatically goes to the last row in DateRange, offsets by 1 AND Automatically adds the next month. Below is a picture of what I am trying to achieve. Thanks in advance guys. enter image description here

Upvotes: 1

Views: 1446

Answers (1)

Automate This
Automate This

Reputation: 31364

Try using the table to your advantage instead of manually finding the last row and inserting. Here is an example:

Sub Last_Row_Range()
    'Get reference to table
    Dim tbl As ListObject
    Set tbl = Sheets("MainTable").Range("MainTable").ListObject

    'Insert new row in table
    Dim tblRow As ListRow
    Set tblRow = tbl.ListRows.Add(AlwaysInsert:=True)

    'Increment previous date by 1 month and place in new row
    tblRow.Range(1, 1) = DateAdd("m", 1, tblRow.Range(0, 1).Value)
End Sub

Tested

enter image description here

Upvotes: 1

Related Questions