Cells worksheet commands used for a range?

  • Thread starter Thread starter Jethro[AGHL]
  • Start date Start date
J

Jethro[AGHL]

Is it possible to use the cells command with a variable in a macro to
specify a data range so that the For-Next loop will increase the value
by 1 to step to the next row?

I am creating a macro to cut\paste\transpose data in a spreadsheet and
am trying to automate it with a macro.
I have used the cells command (e.g. Cells(x, 1).Select) with a For-Next
loop to change the selected row but now I'm trying to select a range
that will also adjust with the loop.
trying to convert this 'Range("A3:F3").Select' into a Cells command to
change with each loop
I tried Range((Cells(x, 1):(Cells(x, 6)).Select but of course gives
invalid formula.

Any ideas how to create a floating range with variable?

Thanks,
Jeff
 
How about:

dim x as long
x = 3
with activesheet
.range(.cells(x,"A"),.cells(x,"F")).select
'or
.range(.cells(x,1),.cells(x,6)).select
end with

..cells is very friendly. It'll take letters or numbers for the column part.
 
Dave said:
How about:

dim x as long
x = 3
with activesheet
.range(.cells(x,"A"),.cells(x,"F")).select
'or
.range(.cells(x,1),.cells(x,6)).select
end with

.cells is very friendly. It'll take letters or numbers for the column part.
Click to expand...

That's the ticket Dave.
Thanks,
Jeff
 
Back
Top