Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count - 1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jacqui" wrote in message
...
I would like to resize an existing range without having to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so that the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it resized
my range so that it became A to H, ie 8 columns. Not very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui