CODEWITHSUNDEEP
excelrangevba
JoshD
JoshD

Reputation: 33

excel VBA function to select the first row in a column and it will will select all cells below it and make it range

I'm trying to write a code, I believe it needs to be a function. I want to select the first row in a column and it will create a range with all the rows below it (until empty cell). I think the problem is the syntax for the 'Range'.

(Ultimately my goal is to be ably to select two cells in two different columns, automatically create 2 ranges and plot them against each other in a scatter plot. but I'm struggling with just the first part)

My code looks something like:

`

Function rng1(x as variant)

Dim ji as string, jf as string
Dim rng1 as range

ji=x.Address
jf=x.End(xldown).Address

rng1=Range(ji:jf)

'

Upvotes: 0

Views: 2517

Answers (1)

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

I removed the variable declaration for "rng1" since you can't name a variable the same as your function. You simply need to concatenate your strings together. You also have to set the function since a range is an object type and not a primitive.

Function rng1(x as variant) as Range

Dim ji as string, jf as string

ji=x.Address
if x.offset(1,0) <> "" then
    jf=x.End(xldown).Address
else
    jf=ji
end if

set rng1=Range(ji & ":" & jf)
End Function

Upvotes: 1

Related Questions