View Full Version : [SOLVED:] Help with Offset and r Resize formulas to select a range.
LutonBarry
01-03-2018, 04:21 PM
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
01-03-2018, 04:52 PM
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
01-03-2018, 05:18 PM
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
01-03-2018, 05:35 PM
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
01-04-2018, 06:03 PM
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
01-05-2018, 03:26 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.