[SOLVED] VBA Number of rows based on number

VBA Number of rows based on number

Hej, I am trying to resize a specific range so that the number of rows in it correspond to a certain number. I wanted to try and do it with an increasing/decreasing loop, but that seems a bit inefficient to me. Is there a way to automatically increase/decrease the number of rows to a certain value? In my case it is supposed to be dependent on the number of John Does I have found one solution, but was hoping for a more elegant one

Public Function AdjRowsNos(Daycount As Integer)

Dim DayCount2, DayCount3 As Integer
With Sheet1
    DayCount2 = .Range("A:A").Find("Additional Expenses").Row - 6
    DayCount3 = Daycount + 18 - DayCount2
    If DayCount3 > 0 Then
        .Range("A18").Resize(DayCount3, 11).Insert , xlFormatFromRightOrBelow
    ElseIf DayCount2 < 0 Then
        .Range("A18", Range("A18").Offset(-DayCount3)).EntireRow.Delete
    End If
End With
End Function

Solution

Sub AdjRowsNos(Daycount As Long)
    Const ROW1 = 18
    Dim r As Long
    With Sheet1
        r = .Range("A:A").Find("Additional Expenses").Row
        .Rows(ROW1).Resize(Daycount + 7).Insert
        .Rows(ROW1).Resize(r - ROW1 + 1).Delete
    End With
End Sub