How to subtract some rows from a range

[Simon Lenn]

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

 

[Charles Williams]

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

 

[Frank Kabel]

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,last_column))
rng_reduced.select
end sub

 

[Frank Kabel]

Hi
use Charles approach, definetly better