Macro Autofill Dynamic Range with Multiple Data Sets

**AliveNThisMoment** · 08-05-2010, 01:22 PM

Hi, everyone!

I have researched for the past three days on this matter and have not discovered a post similar, so I'm deciding to just post a new thread and ask myself.

I am attempting to use macro to autofill two different ranges in the same spreadsheet from the command of a button. Since my ranges can change in the amount of rows that have data, I would like it to autofill for a dynamic range. I have tried many different codes, and I have one that works, but it doesn't work exactly as I had hoped. Here is my information:

Range 1 is from F4:J50. I currently only have data from F4:J15.
Range 2 is from F54:J80. I currently only have data from F54:J60.
(*I will never need more than 50 rows per range)

This is the code that I have currently:

Sub Recalc1()

'Recalc1 Macro

Dim Lastrow As Long

Lastrow = Range("F" & Rows.Count).End(xlUp).Row
Range("F4:J4").AutoFill Destination:=Range("F4:J" & Lastrow)

End Sub

This code works perfectly except it stops at the end of Range 2. I need it to stop right before Range 2 and then start up again at the beginning Range 2. Is there any way that I can have the autofill stop at the end of each range, but still adjust to each dynamic range?

I would post my spreadsheet, but I work for a government agency and it has classified information. If it would make it much easier for everyone, I can make an example spreadsheet using different data.
I really appreciate your help, and I hope I have been clear.
Thank you!
Rae

**StephenR** · 08-05-2010, 02:24 PM

Yes, upload an example spreadsheet please.

**AliveNThisMoment** · 08-06-2010, 01:49 PM

Alright, here's my sample spreadsheet. As you can see, Line 2 begins at Row 51. I am trying to have my first range (Line 1) autofill from F4:J4 to F50:J50 for only the lines that have data. I was able to get that working, but my problem was Line 2. The code that worked to autofill Line 1 was

Sub Recalc()
'Recalc Macro
Range("F4:J4").AutoFill Range("F4:J" & Range("F1").CurrentRegion.Rows.Count)
End Sub

I tried changing the range values to Line 2 data, but because this equation is counting all of column J, it doesn't seem to work. When I use the following code, it autofills up.

 Sub Recalc1()
'Recalc1 Macro
Range("F54:J54").AutoFill Range("F54:J" & Range("F54").CurrentRegion.Rows.Count)
End Sub

I would really love Line 2 to autofill down to F8:J80, as well as keeping Line 1 functioning.

Thank you so much for your help!
Rae

**AliveNThisMoment** · 08-06-2010, 01:58 PM

I'm really sorry. The demo sheet I posted has some graphs on it that were pulling from defined names, neither of which are working with the sheet I just posted. I removed the graphs, so the new spreadsheet should be a little easier to work with.

I'm sorry!
Rae

**StephenR** · 08-06-2010, 06:33 PM

Rae - try this. Not entirely sure what you are copying, but this may give you an idea of how to circumvent the issue of having two blocks of data.

Option Explicit

Sub Recalc1()

Dim Lastrow As Long, Lastrow2 As Long

Lastrow = Range("F50").End(xlUp).Row
Range("F4:J4").AutoFill Destination:=Range("F4:J" & Lastrow)

Lastrow2 = Range("F" & Rows.Count).End(xlUp).Row
Range("F54:J54").AutoFill Destination:=Range("F54:J" & Lastrow2)

End Sub