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