Resize based on activecell offset and last row

[288enzo]

Hi all,

After some searching I found the below that gets me close to my objective. How would I incorporate offset in the code? Meaning, if my active cell is C3, how could I copy activecell.offset(-1,0), in this example C2 and autofill to the last row?

LR = Range("B" & Rows.count).End(xlUp).Row
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":C" & LR)

I tried

ActiveCell.Offset(-1, 0).AutoFill Destination:=Range(ActiveCell.Address & ":C" & LR)

In the below I would copy Yes from C2 to C3:C20

.xlsm
	A	B	C
1
2		INFO	YES
3		INFO
4		INFO
5		INFO
6		INFO
7		INFO
8		INFO
9		INFO
10		INFO
11		INFO
12		INFO
13		INFO
14		INFO
15		INFO
16		INFO
17		INFO
18		INFO
19		INFO
20		INFO
Sheet11

Thank you,

 

[Joe4]

You do not need Autofill. You can apply any value or formula to a whole range at once, i.e.

LR = Range("B" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR).Value = "YES"

 

[288enzo]

You do not need Autofill. You can apply any value or formula to a whole range at once, i.e.

LR = Range("B" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR).Value = "YES"

Thank you but the value can change as can the location of the active cell in column C. It needs to be dynamic, no matter which cell is active in column C, the macro will copy the cell above and fill down to the last row based on column B.

 

[Joe4]

You can use this, which I have documented to describe what each step is doing:

    Dim LR As Long
    Dim C As Long
    Dim R As Long
    
'   Find current column and row
    C = ActiveCell.Column
    R = ActiveCell.Row
    
'   Find last row in column to the left of active cell
    LR = Cells(Rows.Count, C - 1).End(xlUp).Row

'   Fill column from activecell to last row
    Range(Cells(R, C), Cells(LR, C)).Value = ActiveCell.Offset(-1, 0).Value

 

[288enzo]

You can use this, which I have documented to describe what each step is doing:

    Dim LR As Long
    Dim C As Long
    Dim R As Long
   
'   Find current column and row
    C = ActiveCell.Column
    R = ActiveCell.Row
   
'   Find last row in column to the left of active cell
    LR = Cells(Rows.Count, C - 1).End(xlUp).Row

'   Fill column from activecell to last row
    Range(Cells(R, C), Cells(LR, C)).Value = ActiveCell.Offset(-1, 0).Value

Brilliant, thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help!