Solved: subtracting 1 row from a range

[Koesper]

Hi!

I think i'm just overlooking the obvious, because i can't seem to figure out how i can define a range and then removing 1 row from it (the top one)

my vba tool needs to do quite a lot things to certain elements in a workbook, but (almost) never to items in row 1 (the headers)

is there an easy way to define a range for this?

dim myRange as range
set myRange = activesheet.usedrange 'selects the whole used range
set myRange = activesheet.usedrange.rows(1) 'only selects the first row

'this is what i hoped for:
dim countRows as int
countRows = activesheet.usedrange.rows.count()
set myRange = activesheet.usedrange.rows(2 to countRows) 'doesnt work

'or perhaps something like
set myRange = activesheet.usedrange.offset("RowOffset:=1") 'doesnt work, shifts the range one row down, but then i end up with an empty row at te bottom

'or perhaps
set myRange = activesheet.usedrange - activesheet.usedrange.rows(1) 'but this also wont work


what simple angle have i overlooked?http://vbaexpress.com/forum/images/smilies/banghead.gif

[Bob Phillips]

Dim myRange As Range
Dim countRows As Long
countRows = ActiveSheet.UsedRange.Rows.Count
Set myRange = ActiveSheet.UsedRange.Rows(2).Resize(countRows - 1)

[mvidas]

Personally I always use Dim myRange As Range
Set myRange = Intersect(ActiveSheet.UsedRange, Rows("2:65536"))Matt

[Koesper]

http://vbaexpress.com/forum/images/smilies/notworthy.gif great! i tried both options and they both do exactly what i want!

thanks a lot! http://vbaexpress.com/forum/images/smilies/cloud9.gif