Microsoft Excel VBA code range based on cell contents

Question

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?

It sounds like your question is not complete. As if you are manually doing something that can be easily automated. Why are you typing a range to autofill? Can't this be done automatically? Is "24" the last row or is there something else that makes "24" the number? — Andreas
@Andreas I would ideally like the range to be the first X cells in A which have data in them. This will change, is there any easy fix? — Tony Chivers
Yes that is an easy fix. But what is X cells? You need to be more specific on what makes X. Do you mean to the last filled row in column A — Andreas

QHarr QHarr · Accepted Answer · 2018-11-11T20:07:21

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

Microsoft Excel VBA code range based on cell contents

1 Answers