none
Resize a range in a UDF RRS feed

  • Question

  • Hi,

    Referring to this thread:

    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

Answers

  • 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
    
            sAddr = Range(Cells(lTopRow, .Columns(1).Column), Cells(lLastRow, .Columns(1).Column)).Address
    
        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 JP Ronse Thursday, December 27, 2018 2:54 PM
    Wednesday, December 26, 2018 7:11 PM
    Moderator

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

    For more information, please see the following links:

    Resize a range of values

    [VBA UDF] Determine Range To Return Array

    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,

    Thanks for the reply but that doesn't answer my question. 

    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
    
            sAddr = Range(Cells(lTopRow, .Columns(1).Column), Cells(lLastRow, .Columns(1).Column)).Address
    
        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 JP Ronse Thursday, December 27, 2018 2:54 PM
    Wednesday, December 26, 2018 7:11 PM
    Moderator
  • 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