Hi Barb

You can have Excel detect the data range herself with CurrentRegion, like
this:

Sub test()
Dim PullRange As Range
Set PullRange = Cells(1, 1).CurrentRegion
'code here, instead of
MsgBox PullRange.Address(True, True, xlR1C1), , "Data is he"
End Sub

The date conversion is done by helpful Excel cells. Would need to see the
Pull code and the nature the data to suggest a workaround.

HTH. best wishes Harald

"The Moose" skrev i melding
oups.com...
Two questions:

(1) I am pulling an on-line excel spreadsheet into a local spreadsheet
by using a macro that contains R1C1 reference. Right now, I'm
specifying R1C1:R3000C10 as the range to pull in. This on-line
spreadsheet changes daily -- additions/deletions ... so the number of
columns is always the same, but, the number of rows varies. I'd like
the macro to pull in all available data rather than specifying
R1C1:R3000C10. How can I reference that in the macro??

The line in the macro that's specifying where to get the info is:
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='URL[name of spreadsheet file.xls]products'!R1C1:R3000C10"

(2) When I pull in the info, some of the product numbers are converting
to dates. It seems to be only the ones that 'resemble' dates. For
example, product number 1-6645 is entered into the cell as 1/1/6645 and
displays as Jan-45. The cell in the online spreadsheet is formatted as
'text'. If I edit the cell in my own spreadsheet and put a
single-quote and delete the forward-slashes and delete the 'day' and
enter the dash in the appropriate place, it fixes it. Needless to say,
I don't want to waste my life-force doing that :GRIN: I have no
control over how the original data is formatted. How can I FORCE the
data to enter correctly once I import it??

Thanks.

Barb

PS: I'm using Excel 2000 on top of Windows ME.