User defined range in vba

I like to define a range like
Range("A1:value in C1").Select
where A1 is the cell address for the beginning of the range and the end of the range is the value in cell C1 (example C560)

How can I define the end of the range?

Almir's picture

Bionicle's solution is what you need

Range("A1:" & Range("C1")).Select, or (shorter):
Range("A1:" & [C1]).Select

Response expected

Awaiting for your reply

Suggestion from bionicle


You can define a string variable like this and use it for range selection:

Dim a As String
a = "A1" & ":" & Range("C1").Value '

best regards,

Setting Range in vba

You can define the end of your range as
Hope the following explanation helps you.
Dim LastRecNum As Long 'declare variable to hold last row number
Dim rng As Range 'declare variable to denote data range
Dim FiltRng As Range 'declare variable to denote filtered rows filtered from data range
LastRecNum = Cells(Rows.Count, "C").End(xlUp).Row 'to go to last data cell inspite of blank cells
Set rng = ActiveSheet.Range("A1:C" & Trim(Str(LastRecNum))) 'range is set from a1 to last row. You can replace last row number with a number of your choice, 560 as you have mentioned.
'Usage of range
rng.AutoFilter Field:=14, Criteria1:="*" & varEml & "*" 'field number 14 is calculated from the one set in rng 'varEml has the Email Id which autofilter uses to filter rows from the data range
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("CP1:DC4"), Unique:=False 'search data is mentioned in cp1:dc4, which the advance filter uses to filter data from data range
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow 'Copied Filtered to Filter Range variable
FiltRng.Copy Worksheets("FltrCpy").Range("A1") 'COPYING FILTERED data from Filter Range to another Sheet
I am still a learner in vba.