# Resize a range in a UDF • ### Question

• Hi,

ActualUsedRange

I have been breaking my head now for a few days now to find a way to reduce a specified range to the actual range.

Given the UDF:

Function ClosestVal(LookupValue As Range, LookupArray As Range, Optional Condition)

Sample Workbook

This UDF is a kind of VLOOKUP. The problem is the size of LookUpArray. Most users will select whole columns for the LookupArray but doing so can return the wrong value.

Therefor I need to reduce the LookupArray to the range that contains data (constants or formulas). But apparently this is not possible. The code I posted in the referred thread returns the LookupArray range.

The only thing that seems to work  is INTERSECT(LookupArray, LookupArray.Parent.UsedRange).

But also this is not reliable. Any thoughts how it can be done?

Merry Christmas.

Wkr,

JP Ronse

Monday, December 24, 2018 12:47 PM

• If I follow, you might not only want to resize the range but offset it as well. From a quick look at your workbook function looks like you want the address of the resized range, various ways but something like this (assumes only dealing with a single column)

```    With LookupArray

lTopRow = .Rows(1).Row
lLastRow = .Rows.Count

If IsEmpty(.Cells(1, 1)) Then
lTopRow = .Cells(1, 1).End(xlDown).Row
End If

If IsEmpty(.Cells(lLastRow, 1)) Then
lLastRow = .Cells(lLastRow, 1).End(xlUp).Row
Else
lLastRow = lLastRow + .Rows(1).Row - 1
End If

End With```

In passing your UDF might process significantly faster by looping the cells to get the closest values rather than use of 5 or 6 worksheets functions in Evaluate. Also no need to call Evaluate twice to get the same result, simply

```v = Evaluate(strMsg)
If VarType(v) = vbError Then
ClosestVal = CVErr(xlErrNA)
Else
ClosestVal = v
End If
```

• Marked as answer by Thursday, December 27, 2018 2:54 PM
Wednesday, December 26, 2018 7:11 PM

### All replies

• Hi JP Ronse,

Please refer to the following code:

```Function ResizeRange(rng As Range, r As Single, c As Single)
Dim rngV As Variant
Dim tbl() As Variant
Dim Value As Variant
rngV = rng.Value
If r = 0 Then
r = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / c, 0)
ElseIf c = 0 Then
c = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / r, 0)
End If
ReDim tbl(1 To r, 1 To c)
r = 1
c = 0
rngV = Application.Transpose(rngV)
For Each Value In rngV
If c = UBound(tbl, 2) Then
r = r + 1
c = 0
End If
c = c + 1
tbl(r, c) = Value
Next Value
ResizeRange = tbl
End Function```

Resize a range of values

Resize and Rename a Range in UDF

UDF returns array that is too large or too small for the calling range

Hopefully it helps you.

Best Regards,

Lina

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

Tuesday, December 25, 2018 6:08 AM
• Hi Lina,

What I need is when a user enters column ranges as input for the UDF that this input is reduced to the range that contains data.

Say, there is data in the range(A1:B10) but the user has put this as =MyUDF(\$A:\$B) , I would like that the UDF is only take A1:B10 in consideration.

Wkr,

JP

Tuesday, December 25, 2018 1:34 PM
• If I follow, you might not only want to resize the range but offset it as well. From a quick look at your workbook function looks like you want the address of the resized range, various ways but something like this (assumes only dealing with a single column)

```    With LookupArray

lTopRow = .Rows(1).Row
lLastRow = .Rows.Count

If IsEmpty(.Cells(1, 1)) Then
lTopRow = .Cells(1, 1).End(xlDown).Row
End If

If IsEmpty(.Cells(lLastRow, 1)) Then
lLastRow = .Cells(lLastRow, 1).End(xlUp).Row
Else
lLastRow = lLastRow + .Rows(1).Row - 1
End If

End With```

In passing your UDF might process significantly faster by looping the cells to get the closest values rather than use of 5 or 6 worksheets functions in Evaluate. Also no need to call Evaluate twice to get the same result, simply

```v = Evaluate(strMsg)
If VarType(v) = vbError Then
ClosestVal = CVErr(xlErrNA)
Else
ClosestVal = v
End If
```

• Marked as answer by Thursday, December 27, 2018 2:54 PM
Wednesday, December 26, 2018 7:11 PM
• Hi Peter,

Thanks for the feedback. You are right, this is what I am looking for.

I'll think about the other tips.

Kr,

JP

Thursday, December 27, 2018 2:54 PM