Copying a fixed range of cells down a blank column until the last row of data in another column

Question

I have a worksheet (Sheet1) with 4 columns (A:D). In column A there is more than a 1000 lines of data and column D is blank. On Sheet2 I have a range called Indicators (B2:B11) which I need to copy into column D on Sheet1 and repeat the process in the next empty cell in column D all the way down to the last row of data in column A. This is the code I used to copy the range into column D but I have difficulty in repeating the process all the way down as I am not sure how to use the LastRow in this context.

Sub FillDownIndicators()
    Dim LastRow As Long
    LastRow = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row

    Sheet3.Range("Indicators").Copy
    Sheet7.Range("D2").PasteSpecial Paste:=xlPasteValues
End Sub

In short once the range is copied into Column D (10 rows) it must be copied again in the next empty row in column D until the last row is reached in column A. Can someone please help?

HTH · Accepted Answer · 2020-04-01 10:06:28Z

0

trying to pastespecial the content of n cells into m cells wouldn't fill the whole wanted range

so I came up with the following

Option Explicit

Sub FillDownIndicators()
    Dim LastRow As Long

    With Sheet7
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        With .Range("D2:D" & LastRow)
            .ClearContents
            .Resize(Sheet3.Range("Indicators").Rows.Count).Value = Sheet3.Range("Indicators").Value
            With .SpecialCells(xlCellTypeBlanks)
                .FormulaR1C1 = "=R[-" & Sheet3.Range("Indicators").Rows.Count & "]C"
                .Value = .Value
            End With
        End With
    End With
End Sub

a more compact version of which would be:

Sub FillDownIndicators()
    With Sheet7.Range("D2:D" & Sheet7.Cells(Rows.Count, 1).End(xlUp).Row)
        .ClearContents
        .Resize(Sheet3.Range("Indicators").Rows.Count).Value = Sheet3.Range("Indicators").Value
        With .SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=R[-" & Sheet3.Range("Indicators").Rows.Count & "]C"
            .Value = .Value
        End With
    End With
End Sub

answered Apr 1, 2020 at 10:06

HTH

2,0311 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HTH Over a year ago

@Louw, any feedback?

Pᴇʜ · Accepted Answer · 2020-04-01 08:47:14Z

0

Just use your variable LastRow to build that range

Sheet7.Range("D2:D" & LastRow).PasteSpecial Paste:=xlPasteValues

answered Apr 1, 2020 at 8:47

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

3 Comments

HTH Over a year ago

trying to pastespecial the content of n cells into m cells wouldn't fill me the whole wanted range

Pᴇʜ Over a year ago

@HTH True m needs to be n*x where x is an integer number. Then it should work. • In words: If you copy 10 cells then paste space needs to be 10, 20, 30, 40, … cells.

HTH Over a year ago

I think a preface/warning in the answer would be more appropriate then leaving this subject in comments. But that's just my opinion

Collectives™ on Stack Overflow

Copying a fixed range of cells down a blank column until the last row of data in another column

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related