I’m trying to setup a routine to check if a range has non-blank content on a “random” sheet. I’m passing the Sheet the range is located, the range to be searched (actually, the the Row/Column and the Top/Left and Bottom/Right corners of said range). Here is the code segment;

    Function ckEmpty(pPrime As Integer, _
                     pTL As Integer, _
                     pBR As Integer, _
                     Optional pDir As Variant = xlByRows, _
                     Optional pSheet As String = "", _
                     Optional pSpace As Boolean = True) As Integer
        dim wRange as range

        With Worksheets(pSheet)
            If (pDir = xlByRows) Then
                If (pBR < 1) Then
                    Set wRange = .Range(cells(pTL, pPrime), cells(99999, pPrime).End(xlUp))
                Else
                    Set wRange = .Range(cells(pTL, pPrime), cells(pBR, pPrime))
                End If

            elseif (pDir = xlByColumns) then
                ...

            end if

            ...

I’ve tried using;

set wRange = Sheets(pSheet).range ..., and

set wRange = Worksheets(pSheet).range ..., and

set wRange = pSheet.range ...

I also tried hard coding [Worksheets("SheetName").Range...] instead of using a variable, with the same results

I also tried defining a worksheet variable, assigning that to the required Sheet, and yada-yada-yada.  Same results.

I receive a “Application-Defined or Object Defined error (Runtime 1004)” error on the set wRange statement UNLESS I’m on the sheet the range is located (pSheet = Activesheet.name). Using the pSheet.Range reference receives the expected “compile error,” but I had to try 🙂

I’m doing it this was (instead of passing the resolved range as a parameter) because a) that still gave me issues with defining a range from a different sheet, but mainly b) to simplify the calling syntax as defining the range there is considerably longer than just defining it’s location.

As the purpose is to check the range for content no matter what sheet the range is on, and switching sheets specifically for this function call would be tedious, time consuming and rather seizure inducing with the screen flashing – not so LOL.

???