Filldown top cell to last row with data in adjacent col

[Max]

I've entered a value or a formula into AD14 - the 1st/top cell. I want to
fill down to the last row with data in the adjacent col AC. What's the code
to do this? Thanks.

 

[JLGWhiz]

I didn't try this, but it should work.

Sub FD()
lastRw = Cells(Rows.Count, "AC").End(xlUP).Row
ActiveSheet.Range("AD14:AD" & lastRw).FillDown
End Sub

 

[GTVT06]

I've entered a value or a formula into AD14 - the 1st/top cell. I want to
fill down to the last row with data in the adjacent col AC. What's the code
to do this? Thanks.

Hello, Try this.

Sub filldown()
Dim Lrow As Long
Lrow = Range("AC" & Rows.Count).End(xlUp).Row
Range("AD14").FormulaR1C1 = "Your Formula"
Range("AD14").Select
Selection.AutoFill Destination:=Range("AD14:AD" & Lrow),
Type:=xlFillDefault
End Sub

 

[Max]

Thanks, it does work. Could it be generalized, so that all I need to do is
to select the 1st/top cell in any col (except col A, of course), then run
the sub to fill it down? Thanks

 

[Max]

Thanks, your offering works, too. Re-my response to JLGWhiz, I'm seeking to
generalize it. So that all I need to do is to select the 1st/top cell in any
col (except col A, of course), then run the sub to fill it down?

 

[Jim Cone]

Max,
re: "generalize it"
You might want to see how Dick Kusleika did it here...
http://www.dailydoseofexcel.com/archives/2007/11/25/select-adjacent/

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Max"
wrote in message
I've entered a value or a formula into AD14 - the 1st/top cell. I want to
fill down to the last row with data in the adjacent col AC. What's the code
to do this? Thanks.

 

[Max]

Jim, thanks. Afraid I can't access that link in office. It's blocked
(darn!).
Will check it up back home.

 

[GTVT06]

Jim, thanks. Afraid I can't access that link in office. It's blocked
(darn!).
Will check it up back home.

Try this... Ofcourse it'll give an error if placed in Column A

Sub filldown()

Dim Lrow As Long
Dim Col As Variant
Dim i As Variant
Dim lCol As Variant
With ActiveCell
i = .Address
End With
lCol = Mid(ActiveCell.Offset(0, -1).Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Offset(0, -1).Columns.Address, 2) - 2)
Col = Mid(ActiveCell.Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Columns.Address, 2) - 2)
Lrow = Range(lCol & Rows.Count).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Your Formula"
ActiveCell.Select
MsgBox (i & ":" & Col & Lrow)
Selection.AutoFill Destination:=Range(i & ":" & Col & Lrow),
Type:=xlFillDefault
End Sub

 

[GTVT06]

Jim, thanks. Afraid I can't access that link in office. It's blocked
(darn!).
Will check it up back home.

Sorry. Use this one. I just removed the msgbox I was using to help me
while writing the code

Sub filldown()

Dim Lrow As Long
Dim Col As Variant
Dim i As Variant
Dim lCol As Variant
With ActiveCell
i = .Address
End With
lCol = Mid(ActiveCell.Offset(0, -1).Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Offset(0, -1).Columns.Address, 2) - 2)
Col = Mid(ActiveCell.Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Columns.Address, 2) - 2)
Lrow = Range(lCol & Rows.Count).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Your Formula"
ActiveCell.Select
Selection.AutoFill Destination:=Range(i & ":" & Col & Lrow),
Type:=xlFillDefault
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Jim, thanks. Afraid I can't access that link in office. It's blocked

Sorry. Use this one. I just removed the msgbox I was using to help me
while writing the code

Sub filldown()

Dim Lrow As Long
Dim Col As Variant
Dim i As Variant
Dim lCol As Variant
With ActiveCell
i = .Address
End With
lCol = Mid(ActiveCell.Offset(0, -1).Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Offset(0, -1).Columns.Address, 2) - 2)
Col = Mid(ActiveCell.Columns.Address, 2,
WorksheetFunction.Find("$", _
ActiveCell.Columns.Address, 2) - 2)
Lrow = Range(lCol & Rows.Count).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Your Formula"
ActiveCell.Select
Selection.AutoFill Destination:=Range(i & ":" & Col & Lrow),
Type:=xlFillDefault
End Sub

I think we can shorten that a bit...

Sub FillDown()
ActiveCell.Formula = "Your Formula"
ActiveSheet.Range(ActiveCell.Address & ":" & Cells(Rows.Count, _
ActiveCell.Offset(0, -1).Column).End(xlUp). _
Offset(0, 1).Address).FillDown
End Sub

If the formula already exist in the active cell, then the first line of code
can be omitted (that was the approach JLGWhiz appears to have taken).

Rick

 

[GTVT06]

I think we can shorten that a bit...

Sub FillDown()
  ActiveCell.Formula = "Your Formula"
  ActiveSheet.Range(ActiveCell.Address & ":" & Cells(Rows.Count, _
                    ActiveCell.Offset(0, -1).Column).End(xlUp). _
                    Offset(0, 1).Address).FillDown
End Sub

If the formula already exist in the active cell, then the first line of code
can be omitted (that was the approach JLGWhiz appears to have taken).

Rick- Hide quoted text -

- Show quoted text -

Nice code. Thanks Rick!

 

[Max]

Thanks, GTVT06.

Tried running your sub (but with the line below commented out)
' ActiveCell.FormulaR1C1 = "Your Formula"
and it works well.

One further tweak. If I wanted the filldown to the last but one row instead,
ie to stop at one row before: "... End(xlUp).Row" in the adjacent col
how should it be revised? Thanks.

 

[Max]

I think we can shorten that a bit...
Rick, thanks. That's delightful.

If the formula already exist in the active cell, then the first line of
code can be omitted (that was the approach JLGWhiz appears to have taken).

Yes, thanks. I've noted that.

I had one further tweak in my response to GTVT06 which crossed.

If I wanted the filldown to the last but one row instead,
ie to stop at one row before: "... End(xlUp).Row" in the adjacent col
how could your sub be revised? Thanks.

 

[Rick Rothstein \(MVP - VB\)]

Thanks, GTVT06.

Tried running your sub (but with the line below commented out)
' ActiveCell.FormulaR1C1 = "Your Formula"
and it works well.

One further tweak. If I wanted the filldown to the last but one row
instead,
ie to stop at one row before: "... End(xlUp).Row" in the adjacent col
how should it be revised? Thanks.

Using the code modification I posted as a base (with the Formula assignment
statement removed as per your comment above)...

Sub FillDown()
ActiveSheet.Range(ActiveCell.Address & ":" & Cells(Rows.Count, _
ActiveCell.Offset(0, -1).Column).End(xlUp). _
Offset(-1, 1).Address).FillDown
End Sub

Note that the only change between this code and my previously posted code
(the removal of the Formula assignment statement notwithstanding) is in the
**last** Offset call (the row offset was changed from 0 to -1).

Rick

 

[Rick Rothstein \(MVP - VB\)]

I just posted the code to your last response to GTBT06. I'll repeat it here
for sub-thread continuity...


Using the code modification I posted as a base (with the Formula assignment
statement removed as per your comment above)...

Sub FillDown()
ActiveSheet.Range(ActiveCell.Address & ":" & Cells(Rows.Count, _
ActiveCell.Offset(0, -1).Column).End(xlUp). _
Offset(-1, 1).Address).FillDown
End Sub

Note that the only change between this code and my previously posted code
(the removal of the Formula assignment statement notwithstanding) is in the
**last** Offset call (the row offset was changed from 0 to -1).

Rick

 

[Max]

Rick, many thanks, and for the learnings, too. The tweak does it, and your
supportive notes explains what/how it should be tweaked.