Update values till the last active cell of column

RP_1985 · Oct 14th 2015

With the below code, I am able to find last active cell of column E (i.e. E7) and then offset a row (E8). Now what I need is from cell E9 to E12, I need to update as "LED".
The range can vary every time. I have also attached spreadsheet for your workings.

Code

Sub test()
Range("E" & Cells.Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select


With ActiveSheet
    Lastrow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range(.ActiveCell & Lastrow).FormulaR1C1 = "LED"
    End With
      
End Sub

Display More

cytop · Oct 14th 2015

Re: Update values till the last active cell of column

Code

Activecell.Offset(1).Resize(4).Value = "LED"

Perhaps?

RP_1985 · Oct 14th 2015

Re: Update values till the last active cell of column

Thanks. This works if the range is fixed (E9 to E12). If the range varies every time how do I do that ?

cytop · Oct 14th 2015

Re: Update values till the last active cell of column

I can't see how you think that is an absolute reference, it's relative to the active cell as set in the first 2 lines of your code...

Your procedure re-written

Code

Sub test() 
    Range("E" & Cells.Rows.Count).End(xlUp).Select 
    ActiveCell.Offset(1, 0).Select 
    Activecell.Offset(1).Resize(4).Value = "LED" 
     
End Sub

Or, shortened to the Nth degree

Code

Sub test() 
    Range("E" & Cells.Rows.Count).End(xlUp).Offset(2),Resize(4).Value = "LED" 
End Sub

jindon · Oct 14th 2015

Re: Update values till the last active cell of column

Code

Sub test()
    With [a2].CurrentRegion
        With .Columns(.Columns.Count)
            On Error Resume Next
            .SpecialCells(4).Value = "Led"
            On Error GoTo 0
        End With
    End With
End Sub

RP_1985 · Oct 14th 2015

Re: Update values till the last active cell of column

Thank you jindon, it works perfectly.

Thank you cytop for your response.

Update values till the last active cell of column

Participate now!

Share