PDA

View Full Version : VBA Code - Autofill with a Dynamic Range



Jurij
07-23-2013, 09:52 PM
Hi guys,

I will make my issue as quick and concise as possible. I have the following code:

Range("A18:Q36000").Select
Selection.ClearContents
Range("A17:Q17").Select
Selection.AutoFill Destination:=Range("A17:Q500"), Type:=xlFillDefault
Range("J1").Select

As you can see I marked the Q500 in bold. I would need the autofill destination to be dynamic, depending on what is entered in the cell marked "Tenure" in Excel. For example if the tenure cell value is 10, I want the range to be: Range("A17:Q[Tenure*12+15]), so Range("A17:Q135"). To help, I had the previous copy paste code that worked but was too slow:

Range("A18:Q36000").Select
Selection.ClearContents
For i = 1 To Range("tenure") * 12 - 2
Range("A17:Q17").Copy
Cells(17 + i, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next

How do I specify that range with autofill? Any assistance appreciated,

thank you,

Jurij

mancubus
07-23-2013, 11:10 PM
welcome to the forum.

try:


Range("A18:Q" & Range("A18").End(xlDown).Row).ClearContents
Range("A17:Q17").AutoFill Destination:=Range("A17:Q" & Range("tenure") * 12 + 15), Type:=xlFillDefault


i used .End(xlDown).Row to return the last filled row, assuming there are no blank cells in column A.



ps: please wrap your codes with code tags before posting.

the codes above pasted/written here as follows. (for demontsration i added spaces before and after CODE and /CODE. pls remove them when posting codes)
[ CODE ]
Range("A18:Q" & Range("A18").End(xlDown).Row).ClearContents
Range("A17:Q17").AutoFill Destination:=Range("A17:Q" & Range("tenure") * 12 + 15), Type:=xlFillDefault
[ /CODE ]

Jurij
07-23-2013, 11:16 PM
Thank you for the reply, worked like a charm. Sorry about the code, I was in a rush while posting, will not happen again.

Best,

Jurij

mancubus
07-24-2013, 12:02 AM
you are welcome.

that's ok. surrounding and indenting make the codes easier to read and understand.

Jurij
07-24-2013, 12:40 AM
Just one more quick question. Does defining the dynamic ranges always work this way? I mean, do you always use "&" after "A17:Q" and then define the Q row number?

mancubus
07-24-2013, 01:33 AM
if you are doing this way, yes.

known part of the range as string between double quotes + ampersand (&) + variable part (mostly, the row number).


here are some different ways to assign a value to a variable named LastRow


Dim LastRow As Long

LastRow = Range("A1").End(xlDown).Row
LastRow = Range("A150").End(xlDown).Row

LastRow = Range("A1048576").End(xlUp).Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastRow = Cells(1048576, 1).End(xlUp).Row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

LastRow = Range("tenure").Value * 12 + 15

Range("A17:Q" & LastRow).Copy
Range("A17:Q" & LastRow).Clear
Range("A17:Q" & LastRow).Font.Bold = True


if you are working with a contiguous range starting at A1, for ex, you can use CurrentRegion property.


Range("A1").CurrentRegion.Clear
'or
Cells(1, 1).CurrentRegion.Clear
'or
Cells(1).CurrentRegion.Clear





Dim LastRow As Long, LastCol As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1, 1), Cels(LastRow, LastCol)).Copy

snb
07-24-2013, 07:42 AM
or


Range("A18:Q18").resize(cells(rows.count,1).end(xlup).row).ClearContents
Range("A17:Q17").AutoFill Range("A17:Q17").resize([tenure] * 12 + 15)

Jurij
07-25-2013, 06:43 AM
Thank you to both!