Fill down a row on VBA

Question

I am trying to find out how to fill down (or copy?) a row (lastUsedRow) up to the last row. However I find myself struggling with designating ranges (especially because I am working on different datasets that have different sizes).

Before

I need to spot the lastUsedRow (lastUsedRow = .Range("A" & .Rows.Count).End(xlUp).Row) - which is row 31 here. It designates the latest row where there was data in column A.

Then I want to tell VBA to fill down until the last row (lastRow) - row 39 - which can be found using lastRow = .Range("E" & .Rows.Count).End(xlUp).Row. It designates the latest row where there was data in column E.

After

Question

VBA recommends to work with Range().FillDown but I struggle with designating the range when coding for changing datasets. More precisely, how to I write down a range that is between lastUsedRow and lastRow?

It would seem that your `fillRange` is not correct, then. If you need to fill it to A39, then why do you define it only to A20? — David Zemens, Jul 26 '16 at 16:41
`Set fillRange = Worksheets("Sheet1").Range("A1:A" & lastRow)`? — David Zemens, Jul 26 '16 at 16:41
@DavidZemens from what he said and shows, maybe more like `Set fillRange = Worksheets("Sheet1").Range("A" & lastUsedRow & ":D" & lastRow)` ? — Mikegrann, Jul 26 '16 at 16:43
@DavidZemens, the excerpt is not related to the visual example. My problem is that I want to automate the process for columns A to D when the range is not known (but where I can infer `lastUsedRow` and `lastRow`) — pdx, Jul 26 '16 at 16:47
Please update your excerpt so that it shows your *actual* attempt and where you are *actually* stuck. — David Zemens, Jul 26 '16 at 16:48
I'm sure it's a pretty straightforward answer but yet I can't figure it out - I have edited my initial post — pdx, Jul 26 '16 at 16:56

score 2 · Accepted Answer · edited May 23 '17 at 11:44

I think you want to fill down Columns A thru D, from the lastUsedRow (defined from Col A) to the lastRow (defined from Col M), using the values from lastUsedRow in columns A:D.

Dim lastRow as Long, lastUsedRow as Long
Dim srcRange as Range, fillRange as Range
With Worksheets("Sheet1")
    lastUsedRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lastRow = .Range("M" & .Rows.Count).End(xlUp).Row
    ' Fill values from A:D all the way down to lastUsedRow

    Set srcRange = .Range("A" & lastUsedRow & ":D" & lastUsedRow)
    Set fillRange = .Range("A" & lastRow & ":D" & lastUsedRow)

    fillRange.Value = srcRange.Value
End With

If you need to preserve formatting, then use the Copy method:

    srcRange.Copy Destination:=fillRange

Note: +1 to you for using See correct way to find the 'last row'

Thanks for taking some time to help me out! However instead of filling down, it erases lastUsedRow (that I am meant to fill down up to the last row) — pdx, Jul 26 '16 at 17:09

Fill down a row on VBA

1 Answers1

Linked