Finding last Cell and Copying down formula in multiple colums

argar418

New Member
Joined
Feb 10, 2013
Messages
3
Good morning,

I have the following code which works but only fills down one row and I want it to fill down to the last row but I cannot work it out.

Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(2).FillDown
End With
Next

Can someone tell me how to get it to copy down to the last cell in all the columns.

Thanks for your help. Andrew

HKDDSHH31-Jan-13HUS8880-896.469637.037-636.555-895.987January2013Comp-112.059ThursdayMidweek
HKDDSHH31-Jan-13LBR27555223398.06869.8011137.4635405.324January2013Transient125.8541ThursdayMidweek
HKDDSHH31-Jan-13LCR91627812.4588403.574192.460831308.494
HKDDSHH31-Jan-13LPP00000000
HKDDSHH31-Jan-13SOV3603110.8105116.1265105.4556332.3927
HKDDSHH31-Jan-13Unknown00000000

<tbody>
</tbody><colgroup><col><col><col span="15"></colgroup>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Try on a copy of your data
Code:
Sub FillMe()
    Dim LstRw As Long
    LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    Range("L2:Q2").AutoFill Destination:=Range("L2:Q" & LstRw), Type:=xlFillDefault
End Sub

Edit: changed the ranges as I assume by the offset that you have headers
 
Last edited:

argar418

New Member
Joined
Feb 10, 2013
Messages
3
Hey Mark,

Thanks for the answer, the issue is the sheet has 30000+ rows in it and will grow, so it errors out when filling from the toplines.

I have worked out if I amend the .Resize bracket to say 250 it works down 250 rows but the data is not standard so I need to resize with a variable rather than a defined number. I think, if that is possible.

Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(250).FillDown
End With
Next
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Why would it error out at filling 30000+ rows? I just filled 200000 rows with formula and got no error.
 

argar418

New Member
Joined
Feb 10, 2013
Messages
3
Hello Mark,

It was not working before as it showed an error but just tried again and it worked perfectly. Thank you so much.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Your welcome, thanks for the feedback.
 

Forum statistics

Threads
1,186,096
Messages
5,955,821
Members
438,218
Latest member
CarpoRaptor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top