Dynamic Range

Hi guys,

I am rather new to all of this and would appreciate some help.

I am trying to create a dynamic range, but just cant get it right:

I want to get the autofill feature for C column stop once it has reached the same number of row entries in column A.

********************************************************

Sub TestDynamicRange()
'
' TestDynamicRange Macro
' TestDynamicRange
'

'
    Range("A1:B6").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Range("C1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Date"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C6"), Type:=xlFillDefault
    Range("C2:C6").Select
End Sub

**********************************************************

Can anyone help me please?

Thanks,

Matt

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I will add them this time only, be sure to read the Rules.

Why not add the Dynamic range manually?

Do you want =TODAY() or a fixed date?

Maybe something like this:

Sub TestDynamicRange()
Dim LR As Long

Range("A1:B6").Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("C1") = "Date"

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR).Formula = "=TODAY()"

End Sub

Putting in =TODAY() into those cells will result in a date that will keep changing. If you want the date to stay the same the next time you open the sheet, change that last line of code to:

Range("C2:C" & LR)= Date

Hi Matt,

One way would be to create a variable to record the number of rows in column A. Assuming the column A values start in row 2 use something like

Dim iARows as Integer
iARows = Range("A2:A" & Range("A" & Application.Rows.Count).End(xlup).Row))

Then substitute

Range("C2:C" & iArows)

for

Range("C2:C6")

Incidentally, you don't need all the .Selects.　It:s more effcient just to address the ranges in question. e.g.

Dim iARows as Integer
iARows = Range("A2:A" & Range("A" & Application.Rows.Count).End(xlup).Row))

Range("A1:B6").Copy
Sheets("Sheet2").Range("C1").PasteSpecial(xlPasteAll)
Application.CutCopyMode = False
Sheets("Sheet2").Range("C1") = "Date"
    Sheets("Sheet2").Range("C2:C" & iArows) = "=TODAY()"

Untested since I only have access to Excel for Mac 2008 at the moment and in their infinite wisdom Microsoft are no longer supporting VBA with Excel!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)