[VBA Help] Set range to first blank row to last row

brobles · Apr 4th 2018

Hello all:

I have a VBA code that inserts a blank row after a date condition is met. What i would like to do next, is set the newly inserted blank row and the last row as my new range.

Could anyone help me out with this?

Thank you in advance.

chirayuw · Apr 4th 2018

code & sample file please

chirayuw · Apr 4th 2018

code and sample file please

brobles · Apr 4th 2018

Code

Sub atest() Dim LR As Long, i As Long
Dim R As Long




LR = Range("C" & Rows.Count).End(xlUp).Row For i = 10 To LR    


If Range("C" & i).Value > ([A7] + 31) Then        


Rows(i).Resize(1).Insert   


Exit For    
End If
Next i

Display More

-- I would then like to run the code below from the last row to the newly inserted row from above--

Code

For R = Cells(Rows.Count, "C").End(xlUp).Row To (Cells(R, "C").Value) = 0 Step -1  
If Year(Cells(R, "C").Value) <> Year(Cells(R - 1, "C").Value) Then
Rows(R).Resize(2).Insert


Next

chirayuw · Apr 5th 2018

I couldn't quite figure out what u were trying to do but im assuming 1st code only adds 1 row, second code works after the blank row & puts 2 rows between diff years? have a look

Code

Sub test()


Dim LR As Long
Dim i As Long
Dim R As Long
Dim x As Long




LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 9 To LR
    If Range("C" & i).Value > ([A7] + 31) Then
        Rows(i).Resize(1).Insert
        Exit For
    End If
Next i




LR = Range("C" & Rows.Count).End(xlUp).Row
x = 0
For R = i + 1 To LR
    If Year(Cells(R, 3)) <> Year(Cells(R + 1, 3)) Then x = x + 2
Next R
LR = LR + (x - 2)


For R = i + 1 To LR
    If Year(Cells(R, 3)) <> Year(Cells(R + 1, 3).Value) Then
        Rows(R + 1).Resize(2).Insert
        R = R + 3
    End If
Next R


End Sub

Display More

[VBA Help] Set range to first blank row to last row

Participate now!

Share