Excel VB-Copy formula down until adjacent cell (left) is blank?

[gittymoe]

Guys, I have daily data pull fills Colum D Row count is always different. I am modifying the data pull through VB, and I also need to find the last cells in columns A:C and copy the data down to the last row in column D. I guess I want it to be dynamic so that as rows decrease/increase the data is only copied down to the final row/record. Below works, however, it starts at A2:C2 every time. Somedays I may have data that starts at A2:C2 while other days It will start at A25:C25 so I need this to be dynamic and pick up the last records in columns A:C.

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
.Range("a2:C2").AutoFill Destination:=.Range("A2:C2" & LastRows & LastRow) _
, Type:=xlFillCopy
End With

End With
End Sub

 

[Anthony47]

And what can we analyze to determine where the data starts from?
For example: Are the rows above the first used row empty? Is there a heading that can be searched ad located? Is that the first row of data in column D? Or what?

Bye?

 

[gittymoe]

Yes, there are headings in Columns A, B and C..InvoiceNumber, InvoiceDate and PONumber respectively. So the first available for each would be A2, B2 and C2 and my thought would be to copy down using column D with the header SKUNumber that with data starting in D2 and may go down some days to D2:D33 or the next day D2:D20. In the below example I would look to have the data in columns A:C copy down based on column D, in this case D4

InvoiceNumber	InvoiceDate	PONumber	SKUNumber
N12345	4/26/2020	1	123456
			1234567
			234567

 

[Logit]

I'm confused.

Are you wanting to copy data in A2:C2 down the same number of rows as there are in Col D that have data already ?
The SKU Numbers are already in the Col from D2:D25 as an example ?

If the above question answer is true ... are you wanting the Invoice Number to increase one number at a time as it is copied in the different rows ?
What about the Invoice Date ? Is that to be increased one day at a time as well ?
And the PONumber is to be increased one number at a time too ?

 

[gittymoe]

There would be no increase in PONumber, InvoiceDate, or InvoiceNumber. Below is what the end results should be. Think of it like this, Ultimately, I am trying to pull in invoice data from one invoice and I need the InvoiceNumber, InvoiceDate, and PONumber (they are only provided once on the invoice) to be copied down in the columns adjacent to the SKUNumbers

InvoiceNumber	InvoiceDate	PONumber	SKUNumber
N12345	4/26/2020	1	123456
N12345	4/26/2020	1	1234567
N12345	4/26/2020	1	234567

 

[Logit]

This is one of many methods :

Option Explicit

Sub test()
Dim C As Range
Application.ScreenUpdating = False

    For Each C In Range("D2:D100") '<-- adjust range here
        If C.Value <> "" Then
            Sheet1.Range("A2:C2").Copy C.Offset(0, -3)
        End If
    Next
    
Application.ScreenUpdating = True
End Sub

 

[Anthony47]

[...] and I also need to find the last cells in columns A:C and copy the data down to the last row in column D. I guess I want it to be dynamic so that as rows decrease/increase the data is only copied down to the final row/record. [...] . Somedays I may have data that starts at A2:C2 while other days It will start at A25:C25

Given the initial information plus those in messages #3 and #5 I guess we are talking about a summary list, populated day after day by appending the new information to those already set in the sheet. So my proposal is this macro:

Sub gittyfill()
Dim LastA As Long, LastD As Long
'
LastA = Cells(Rows.Count, "A").End(xlUp).Row
LastD = Cells(Rows.Count, "D").End(xlUp).Row
'
If LastD > LastA Then
    Cells(LastA + 1, "A").Resize(LastD - LastA, 3).Value = Cells(LastA, "A").Resize(1, 3).Value
End If
End Sub

This will copy the values in the "last available" Ax:Cx near the data in col D

Bye