CODEWITHSUNDEEP
excelvba
Tony Chivers
Tony Chivers

Reputation: 191

Microsoft Excel VBA code range based on cell contents

I have some VBA code, and I have a problem with a range.

In my excel sheet, I want the range to be based on the contents of cell C2.

Here is the VBA code, 

Sub Repeat()
'
' Repeat Macro
'

'
    Range("A1:A3").Select
    Selection.AutoFill Destination:=Range("A1:A19"), Type:=xlFillDefault
    Range("A1:A19").Select
    Range("B1").Select
End Sub

However, instead of A19 I would ideally like to use the AX with X being the value in cell C2, if cell C2 contains 24 I would like it to say A24 instead of A19 any ideas?

Upvotes: 0

Views: 135

Answers (1)

QHarr
QHarr

Reputation: 84465

Try

Range("A1:A" & Range("C2").value)

But you should qualify with the sheet name as well.

For example:

With Worksheets("Sheet1")
    .Range("A1:A3").AutoFill Destination:= .Range("A1:A" & .Range("C2").value)    , Type:=xlFillDefault
End With

Upvotes: 1

Related Questions