Select part of input range

[eviMogwai]

Hey folks!

So I'm programming a function that takes a range as input but during the course of the function I need to use a subset of this range as comparison.

So if I get a range("A20:A46") as input I need get the range without a few of the cells at start and end, so something like range("A24:A40").

Is there an elegant way to do this?

 

[Weaver]

What's the criteria for exclusion?

If you just wanted to chop off the first and last 4 you could use something like

set mySmallerRange = myBiggerRange.offset(4).resize(myBiggerRange.rows.count - 8)

for example.

Whether that counts as elegant or not is purely subjective!

 

[eviMogwai]

The problem should be described correctly in the first post, but if anything's unclear here's an analogy in pseudocode

'-- An arbitrary list as input
myList[] = [a,b,c,d,e,f,g,h,i,j,k]
 
'-- A useful list consisting of a middle part of the input
UsefulList[] = myList[index 2 to index 8]
 
'-- UsefulList is [b,c,d,e,f,g,h]

 

[jim may]

Try..

Sub Foo()
Set OrigInput = Range("A20:A46")
MsgBox OrigInput.Address
Set SubInput = OrigInput.Offset(4).Resize(17, 1)
MsgBox SubInput.Address
End Sub

 

[eviMogwai]

Well, this part of the function accepts 3 lengths of input range and depending on this length I need to trim off a couple of cells from start and end respectively.

Your solutions are neat, but is there a way to do this without knowing the orientation of the range?


If I put my range into an array, would there be an easier way?

 

[jim may]

additional view of possibilities...

Sub RunThisMacroFirst()
x = 4
y = 17
Call Foo(x, y)
End Sub


Sub Foo(x, y)
Set OrigInput = Range("A20:A46")
MsgBox OrigInput.Address
Set SubInput = OrigInput.Offset(x).Resize(y, 1)
MsgBox SubInput.Address
End Sub

 

[eviMogwai]

Thanks for the help.

Although this would mean i first had to check that the input is a column or a row and modify respectively, it's a solution, but kind of cumbersome in my program.

 

[jim may]

Your OrigRange you say you get through "Input" - how do you communicate this Input range to Excel?

Next, to me is what? reveals to you or the system what subRange (cells) that you need/want to identify? I'm not clear on this! (so can't help more)..

 

[eviMogwai]

Next, to me is what? reveals to you or the system what subRange (cells) that you need/want to identify? I'm not clear on this! (so can't help more)..

Sorry, but I don't seem to understand this comment, I'm not a native english speaker, could you please expand?


But I have managed a workaround (and hit another snag), the idea is to put the range through a for loop that picks the interesting cells from the input and union them together into a new range.

function foo(rLevels as Range) as Variant
'-- rLevels is at least 16 cells
 
'-- code goes here
 
'-- init as Union doesn't handle empty ranges
Set rMyLevels = rLevels(4)
 
    For i = 1 To rLevels.Cells.Count
        If i > 4 And i < 20 Then
           Set rMyLevels = Application.WorksheetFunction.Union(rMyLevels, rLevels.Cells(i))
        End If
        Next

I have deliberately put in 2 different ways of referring to a cell,
rLevels(4) and rLevels.Cells(i), with hope that someone could tell me which one is right, both return the value, but what type is it?

When I run the code, i get error 438 " Object Doesn't Support This Property Or Method".
any ideas?

 

[eviMogwai]

Union is not a WorksheetFunction, and then it works
Thanks for the help guys