You could refer to the column like:

activesheet.columns(8)...
instead of
activesheet.range("h:H")...

You can find the last used row of a column with something like:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"B").end(xlup).row
'or
lastrow = .cells(.rows.count,2).end(xlup).row
'.cells is very forgiving.
end with

Then you could use:

dim myRng as range
set myrng = .range(.cells(1,2),.cells(lastrow,2))
....

Then later, you can use:

...., myrng.address(external:=true), ...

There aren't too many (any???) things that have to use R1C1 reference style.

If this didn't help, you may want to post more details.




"Jonnyboy117 <" wrote:

I'm writing my first macro, having no prior knowledge of VB...fun.
Basically, I need the macro to open an input text file of tabulated
data and create a chart with that data.

The problem is that the amount of input data will change frequently, so
the plotting range has to be dynamic. When I recorded the macro, it
inserted absolute R1C1 cell references for the range function. I need
to replace those references (for example, R1C2:R4332C2) with dynamic
references that will select the entire column.

I noticed that you could do this easily in the A1 reference style, by
simply using H:H or whatever the column name is. But I can't find any
information on doing it in the R1C1 style, and it seems I have to use
R1C1 when writing the macro.

Thanks in advance for any help you can offer!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson