CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

Get a subrange that has all the cells of a range except for those on the first row?

I have a range

Set rng = Range("B5:H20")

I want to create a subrange that contains all cells except for those on the first row of rng. What is a good way to do this?

Set subRng = 'Range("B6:H20")

Upvotes: 1

Views: 6129

Answers (5)

Byron Wall
Byron Wall

Reputation: 4010

Another versatile approach to this is the Offset and Intersect pattern. It has the advantage over Resize of working the same regardless of how you do the shift (could move 1 column or row w/o rethinking the Resize part).

It also technically works for discontinuous ranges although a use case for that is rare at best.

Set subrng = Intersect(rng.Offset(1), rng)

Upvotes: 3

Phrancis
Phrancis

Reputation: 2282

I think you're looking for Offset and Resize properties. Like this:

Set rng = Range("B5:H20")
Set subRng = rng.Offset(RowOffset:=1, ColumnOffset:=0).Resize(rng.Rows.Count - 1)

Or shorter (but less clear):

Set subRng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)

Here is a tutorial with more information: http://www.homeandlearn.org/the_offset_property.html

Upvotes: 0

nutsch
nutsch

Reputation: 5962

Use a mix of offset and resize

set rng = Range("B5:H20")
set subrng=rng.offset(1).resize(rng.rows.count-1)

If you just work with rows, you don't need to specify the second parameter (column) in either offset or resize.

Upvotes: 6

Joe Farrell
Joe Farrell

Reputation: 3542

I don't use VBA much so this might not be the simplest way, but it ought to work:

Set subRng = rng.Range(rng.Cells(2, 1), rng.Cells(rng.Rows.Count, rng.Columns.Count))

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

What's the purpose? Your method would work fine with the commented part. You could also set the top row as a variable, and use that...kind of like

dim headerRow as Integer
headerRow = 5
Set rng = Range(Cells(headerRow,2),Cells(20,8))

Set subRng = Range(Cells(headerRow+1,2),Cells(20,8))

Upvotes: 0

Related Questions