How To Delete or Add Rows To A Range Using VBA

[kelly mort]

Using this image as a guide:

The code below here is used to find a given date stored in the variable called "findvalue".

        Dim findvalue As Range, lr&, j&
        Dim exp As Worksheet, c&, i&
        Set exp = Sheets("EXPENSE")
        lr = exp.Cells(Rows.Count, "A").End(xlUp).Row
        If lr < 4 Then lr = 4
        Set findvalue = exp.Range("A4:A" & lr).Find(what:=CDate(cboGetDate), LookIn:=xlValues, lookat:=xlWhole)

Now here are my requests:
1. If I want to delete say two rows between the found date, eg - 26-01-21 and the string "DAILY TOTALS", what are the lines that I need to write?

2. If I want to Add say two rows between the found date, eg - 26-01-21 and the string "DAILY TOTALS", what are the lines that I need to write?

Thanks in advance.

 

[JLGWhiz]

First, let's clarify that 'findvalue' is a range and not a date value, although it may contain a date value. Being that it is a range, you can use it as a reference point to add or delete rows and columns. For example, if you want to delete 'NEWSPAPER' and 'BANK CHEQUE' rows then

findvalue.Offset(2).Resize(2).EntireRow.Delete

would delete those two rows and shift everything up (shift up is default).
Similarly, to add rows you need to use the Insert method.

findvalue.Offset(2).Resize(2).EntireRow.Insert

would insert two rows above "NEWSPAPER'.
The insert function uses the row or column at either the left or top of the specified range as the insertion point and shifts everything either right or downward as the case may be.