I invoke the UsedRange and get the range as A14:B55 but I want to
subtract the first 5 rows from this used range how can I remove the
rows A14:B20 from the above range.

Thanks
Simon

dim oUsed as range

set oUsed=activesheet.usedrange
set oUsed=oUsed.resize(oused.rows.count-5,2)
set oUsed=oUsed.offset(5,0)


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Simon Lenn" wrote in message
om...
I invoke the UsedRange and get the range as A14:B55 but I want to
subtract the first 5 rows from this used range how can I remove the
rows A14:B20 from the above range.

Thanks
Simon

Hi Simon
Not sure as A14:B20 are more than 5 rows (7 rows) but try
sub foo()
dim rng as range
Dim rng_reduced as range
dim last_row as long
Dim last_column as integer
set rng = ActiveSheet.UsedRange

last_row = rng.rows.count + rng.row -1
last_column = rng.columns.count + rng.column - 1
set rng_reduced =
range(cells(rng.row+5,rng.column),cells(last_row,l ast_column))
rng_reduced.select
end sub


--
Regards
Frank Kabel
Frankfurt, Germany

Simon Lenn wrote:
I invoke the UsedRange and get the range as A14:B55 but I want to
subtract the first 5 rows from this used range how can I remove the
rows A14:B20 from the above range.

Thanks
Simon

Hi
use Charles approach, definetly better :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel wrote:
Hi Simon
Not sure as A14:B20 are more than 5 rows (7 rows) but try
sub foo()
dim rng as range
Dim rng_reduced as range
dim last_row as long
Dim last_column as integer
set rng = ActiveSheet.UsedRange

last_row = rng.rows.count + rng.row -1
last_column = rng.columns.count + rng.column - 1
set rng_reduced =
range(cells(rng.row+5,rng.column),cells(last_row,l ast_column))
rng_reduced.select
end sub



Simon Lenn wrote:
I invoke the UsedRange and get the range as A14:B55 but I want to
subtract the first 5 rows from this used range how can I remove the
rows A14:B20 from the above range.

Thanks
Simon