Autofill Dynamic Column with Dynamic Starting Point

MicMac

New Member
Joined
Aug 15, 2019
Messages
1
I'm 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 the starting point of column "X and Y" are dynamic (due to previously imported information) (Column X & Y is never longer than Column Z).
The 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



<code>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


H</code>owever the solution eludes me on how to autofill "X57 to X 89" (same with column "Y") to line up with the number of rows in column "Z"
without using absolute references or knowing where the starting cells in "X" & "Y" are going to be.



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


Background :
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 tables separated and itemized 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 (which will give the ability to apply a lookup. The only common denominators are date and shift)



<code>

</code>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,944
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
   Range(Range("X" & Rows.Count).End(xlUp), Range("Z" & Rows.Count).End(xlUp).Offset(, -1)).FillDown
 

Forum statistics

Threads
1,086,055
Messages
5,387,542
Members
402,070
Latest member
wentpow

Some videos you may like

This Week's Hot Topics

Top