Autofill Dynamic Column

Question

Trying to Autofill Column X and Column Y with text Where Column Z determines the table length Starting cell for column Z is "Z3" but column "X and Y" are dynamic Last filled cell in column "X & Y" carries the text required. Current Last cells is "X56" and "Y56" Current last cell in column Z is "Z89"

I can easily get to x56 or y56 using

Range("Y3").Select
Selection.End(xlDown).Select

Selection.AutoFill Destination:=Range("Y56:Y89")
Range("Y56:Y89").Select
Range("X56").Select
Selection.AutoFill Destination:=Range("X56:X89")
Range("X56:X89").Select

However the solution eludes me to remove absolute references due to the dynamic nature of the information being imported and added to the column of previous information.

I tried this code i read through my research but couldn't make it work

lastRow = Range("Y3").End(xlDown).Row

Selection.AutoFill Destination:=Range("Y3:Y" & lastRow), Type:=xlFillDefault

Any assistance would be really appreciated as this appears to be the lynch pin to completing this task Cheers Mick

with I am trying to stack reports generated on a 12 hourly basis. into an accrued 24 data table. This will then be accrued into a monthly data table.

As base information is downloaded in csv format. The four reports are formatted differently so i also have to stack the four reports in two table separated and itemised by date and shift.

This then allows the use of lookups, countifs sumifs etc to populate my outputs.

The four reports are dynamic and open to the potential of having a number of blank cells throughout.

I have written code that is robust enough to achieve this short of this one issue.

As the four reports do not have time stamps i am forced to use file names (column A:A) to populate the Date and Shift ranges (column A:B) as well as (Column X:Y) but need to drag the text down to cover all rows of information

Range("Y3").Select
Selection.End(xlDown).Select

Selection.AutoFill Destination:=Range("Y56:Y89")
Range("Y56:Y89").Select
Range("X56").Select
Selection.AutoFill Destination:=Range("X56:X89")
Range("X56:X89").Select

Autofill Columns with text without absolute references to allow for dynamic column range and without known starting point on the column

It's quite confusing. Do you want to Auto fill columns X & Y till the last used row in the Column Z ?? — Mikku, Aug 15 '19 at 04:51
Sorry i thought that may be the case, the upload new question format kinda pulled my question into pieces and re-arranged throwing it out of context. But in short yes fill x & y till the last row of z, i wish there was a way i could upload my worksheet to show issue — MickMac, Aug 21 '19 at 06:09

score 0 · Answer 1 · answered Aug 15 '19 at 06:03

Do not use xlDown to find the last row. You may want to have a look at this Finding Last Row

Is this what you are trying? I have given you two option. Take your pick.

Option Explicit

Sub SampleA()
    Dim ws As Worksheet
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Get last row in Col Z
        lRow = .Range("Z" & .Rows.Count).End(xlUp).Row

        '~~> Autofill formula in 1 go
        .Range("X3:X" & lRow).Formula = .Range("X3").Formula
        .Range("Y3:Y" & lRow).Formula = .Range("Y3").Formula
    End With
End Sub

Sub SampleB()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Get last row in Col Z
        lRow = .Range("Z" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("X3:Y3")
        rng.AutoFill Destination:=.Range("X3:Y" & lRow)
    End With
End Sub

Thanks for your feedback Siddharth, i have just returned back to work and haven't had a chance to try and apply your suggestion, but i wanted to say thanks in advance — MickMac, Aug 21 '19 at 05:24

Autofill Dynamic Column

1 Answers1