Help with Offset and r Resize formulas to select a range.

[LutonBarry]

Hello folks,

I have a spreadsheet attached as a sample. The columns in use never vary but the number of rows would.

Data is not entered in every cell in the blue range and it is that range I want to select.

I've had a go at some code using current region select and then the Offset function which moves the selection two columns to the right but then includes columns I and J which I don't want. I then tried the resize function but cannot get that to work.

But basically I want to select the region with data entered in columns C to H.


Sub SelectRange()

Dim myhours As Range

Range("A1").CurrentRegion.Select
Set myhours = Range("A1").CurrentRegion
myhours.Offset(, 2).Select
myhours.Resize(, 2).Select

End Sub

[Paul_Hossler]

Many ways, this is probably the easiest



Option Explicit
Sub SelectRange()

Dim myhours As Range
Set myhours = Range("A1").CurrentRegion
Set myhours = myhours.Cells(1, 3).Resize(myhours.Rows.Count, myhours.Columns.Count - 2)
MsgBox myhours.Address
End Sub

[LutonBarry]

Paul, Thanks very much looks easy when you know how:-)

Looking at the code below does the 'myHours.Cells(1,3)' reset the top left corner of the selection to C1 from A1?
Then the resize element 'columns count - 2' bring the selection back two columns from the right hand side?

Set myhours = myhours.Cells(1, 3).Resize(myhours.Rows.Count, myhours.Columns.Count - 2)

But again thank you very much for your help. I'll mark the thread as solved but would be grateful if yo can clarify on the two questions above.

[Paul_Hossler]

Usually not necessary to .Select something to work on it

Step through the more debuggy version below and see the effects of selecting and not selecting

The myhours (C:H) is not selected until the end, otherwise the Selection doesn't change (Still A1)



Option Explicit
Sub SelectRange()

Dim myhours As Range

Range("A1").Select
MsgBox Selection.Address

Set myhours = Range("A1").CurrentRegion
MsgBox myhours.Address
MsgBox Selection.Address


Set myhours = myhours.Cells(1, 3).Resize(myhours.Rows.Count, myhours.Columns.Count - 2)
MsgBox myhours.Address
MsgBox Selection.Address

myhours.Select
MsgBox Selection.Address

End Sub

[p45cal]

or:
Sub blah()
Dim myhours As Range
Set myhours = Range("A1").CurrentRegion
Set myhours = Intersect(myhours, myhours.Offset(, 2))
myhours.Select
End Subor:
Sub blah2()
Dim myhours As Range
Set myhours = Intersect(Range("A1").CurrentRegion, Range("A1").CurrentRegion.Offset(, 2))
myhours.Select
End Sub

[LutonBarry]

Paul and P45cal Can I thanks you both for your input here it is invaluable.

As Paul intially said there are many ways to solve a problem like this and in so ways that is what causes a novice like me so many problems as you have many alternatives.

But your input has opened many other doors for me.

Thanks and a Happy New Year to you both.