Copy values of range immediately below same range excel vba

[drluke]

In my worksheet I have continuous rows with data in D7:D726. I column E I have data from E7:E24. I need to copy the data
in range E7:E24 and autofill the remaining rows in col E (i.e. E25:E726) with that range of values.

The ranges in both col D and the initial range to be copied in col E will never be the same.

So far I have tried this code:

Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row).Value = Range("E7:E" & Range("E" & Rows.Count) _
.End(xlUp).Row).Value

The result I'm getting (in E25:E726) is #N/A
Any advise much appreciated.

 

[DanteAmor]

Try:

    Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row).AutoFill _
        Destination:=Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row)

 

[drluke]

Thank you Dante.
This works, except that for the first value in the range, which is "50" is incrementally increased everytime the range is
copied. So if I have 50,51,52,53,54,55 in my range, instead of copying that it does 51,51,52,53,54,55 etc.
How can I prevent this?

 

[Fluff]

Try

 Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row).AutoFill _
        Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row), xlFillCopy

 

[DanteAmor]

Thank you Dante.
This works, except that for the first value in the range, which is "50" is incrementally increased everytime the range is
copied. So if I have 50,51,52,53,54,55 in my range, instead of copying that it does 51,51,52,53,54,55 etc.
How can I prevent this?

In this case if you are going to fill a series, then the parameter must be [FONT=&quot]xlFillSeries, try and tell me

    Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row).AutoFill _
        Destination:=Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row), Type:=xlFillSeries

[/FONT]

https://docs.microsoft.com/en-us/office/vba/api/excel.xlautofilltype

[h=1]xlAutoFillType enumeration (Excel)[/h]

• <time role="presentation" datetime="2017-06-08T00:00:00.000Z" data-article-date-source="ms.date" style="box-sizing: inherit;">
  </time>
• Specifies how the target range is to be filled, based on the contents of the source range.

[FONT=&quot][TABLE="width: 708"]
<thead style="box-sizing: inherit;">[TR]
[TH="align: left"]Name[/TH]
[TH="align: left"]Value[/TH]
[TH="align: left"]Description[/TH]
[/TR]
</thead><tbody style="box-sizing: inherit;">[TR]
[TD]xlFillCopy[/TD]
[TD]1[/TD]
[TD]Copy the values and formats from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillDays[/TD]
[TD]5[/TD]
[TD]Extend the names of the days of the week in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillDefault[/TD]
[TD]0[/TD]
[TD]Excel determines the values and formats used to fill the target range.[/TD]
[/TR]
[TR]
[TD]xlFillFormats[/TD]
[TD]3[/TD]
[TD]Copy only the formats from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillMonths[/TD]
[TD]7[/TD]
[TD]Extend the names of the months in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillSeries[/TD]
[TD]2[/TD]
[TD]Extend the values in the source range into the target range as a series (for example, '1, 2' is extended as '3, 4, 5'). Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillValues[/TD]
[TD]4[/TD]
[TD]Copy only the values from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillWeekdays[/TD]
[TD]6[/TD]
[TD]Extend the names of the days of the workweek in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFillYears[/TD]
[TD]8[/TD]
[TD]Extend the years in the source range into the target range. Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlGrowthTrend[/TD]
[TD]10[/TD]
[TD]Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers in the source range are multiplicative (for example, '1, 2,' is extended as '4, 8, 16', assuming that each number is a result of multiplying the previous number by some value). Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlLinearTrend[/TD]
[TD]9[/TD]
[TD]Extend the numeric values from the source range into the target range, assuming that the relationships between the numbers is additive (for example, '1, 2,' is extended as '3, 4, 5', assuming that each number is a result of adding some value to the previous number). Formats are copied from the source range to the target range, repeating if necessary.[/TD]
[/TR]
[TR]
[TD]xlFlashFill[/TD]
[TD]11[/TD]
[TD]Extend the values from the source range into the target range based on the detected pattern of previous user actions, repeating if necessary.[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT]

 

[drluke]

Autofill syntax error

I'm getting a syntax error on this line of code:

Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row).AutoFill Destination:=Range("E7:E" & Range("D" & Rows.Count).End(xlUp).Row), xlFillValues

I can't work out what is wrong. Any advice appreciated.

 

[MARK858]

Re: Autofill syntax error

I am not sure exactly what you are trying to do, can you describe in words what you want the code to do rather than just asking to fix code that doesn't work please.

 

[Fluff]

Re: Autofill syntax error

@drluke
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

On this occasion I have merged your two threads

 

[drluke]

Re: Autofill syntax error

I am not sure exactly what you are trying to do, can you describe in words what you want the code to do rather than just asking to fix code that doesn't work please.

In my worksheet I have continuous rows with data in D7:D726. I column E I have data from E7:E24. I need to copy the data
in range E7:E24 and autofill the remaining rows in col E (i.e. E25:E726) with that range of values.

 

[Fluff]

Re: Autofill syntax error

Did you try my suggestion from post#4?
Also what is the error number & message you get with the code you posted?