Range variable reverting to nothing / SpillingToRange

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
351
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a range variable set to a named range that is consistently reverting to nothing. Earlier it was working fine, but now it isn't.
It may be because at one point it is set using the SpillingToRange. I'm not sure if the mechanics for that works differently than normal.

It seems to work alright in this sub. It is called rngCustomer (not to be confused with rngECustomer):

VBA Code:
    Dim rngECustomer As Range, rngCustomer As Range
    Dim sLookup As String
    Dim matchNo As Long, revMatchNo As Long
    Dim i As Long, x As Long, y As Long
    
    wksExtract.Columns.EntireColumn.Hidden = False          ' If Customer column is hidden, unhide it and others
    
    Set rngHeader = wksExtract.UsedRange.Rows(1).SpecialCells(xlCellTypeConstants)            ' Used Range is all data on a sheet, special cells are non-formulas
    Set rngOutput = wksUpload.Range("Output")                                                 ' Set range variables to named ranges
    Set rngMatches = wksDash.Range("Matches")
    Set rngCustomer = wksDash.Range("Customer")
    Set rngECustomer = wksExtract.Cells.Find("Customer", wksExtract.Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlNext)  'Uses Find function to locate Customer column on Extract worksheet
    Set rngECustomer = rngECustomer.Resize(rngECustomer.Offset(100000).End(xlUp).Row, 1).Offset(1)      ' Changes rngECustomer to encompass entire column of data
    
    rngMatches.Resize(100, 3).ClearContents                 ' Next three clear old data from Uploader workbook
    rngOutput.Resize(100000, rngOutput.Columns.Count).Offset(1).ClearContents
    rngCustomer.ClearContents
                                                            ' Next uses UNIQUE & FIlTER formulas on dashboard to populate
                                                            ' all unique customer names by using the extraction workbook name, worksheet name
                                                            ' and address of the customer column, offset by 1 row.
    rngCustomer.Formula2 = "=FILTER(UNIQUE('[" & wbExtract.Name & "]" & wksExtract.Name & "'!" & _
                           rngECustomer.Address & "),UNIQUE('[" & wbExtract.Name & "]" & wksExtract.Name & "'!" & _
                           rngECustomer.Address & ")<>"""")"

It fails when it reaches rngCustomer.Resize(1000, 2).ClearContents here (I removed code I thought was irrelevant, marked by ...):

VBA Code:
   Dim rngMatches As Range, rngCustomer As Range
    ...
    
    Set wbUpload = Workbooks(wksDash.Parent.Name)       ' Sets Upload workbook variable in case necessary
    Set rngHeader = wksExtract.UsedRange.Rows(1)        ' Same ranges as in getMatches, except rngData is used to set the specific column receiving data
    Set rngOutput = wksUpload.Range("Output")
    Set rngData = rngHeader.Resize(rngHeader.Offset(100000).End(xlUp).Row, rngHeader.Columns.Count)
   ...
    
    If CheckFilters(wksExtract) Then rngData.AutoFilter     ' Calls function CheckFilters with 1 arg, the extraction sheet variable
                                                            ' If it returns True then turn off AutoFilter, which is mandatory.
    Set rngData = rngHeader.Resize(rngHeader.Offset(100000).End(xlUp).Row, rngHeader.Columns.Count)     ' Change rngData to encompass entire column of data
    rngData.AutoFilter Field:=1, Criteria1:=">=" & CDbl(dateStart), Operator:=xlAnd, Criteria2:="<=" & CDbl(dateEnd)    ' Sets the filter to include the chosen dates
    
    On Error Resume Next
   
    ...

    Set rngOutput = wksUpload.Range("Q2:Q" & rowCount)                  ' Set to item rate column
    Set rngCustomer = wksDash.Range("Customer").SpillingToRange         ' Set to customer column on dashboard
    Set rngCustomer = rngCustomer.Resize(rngCustomer.Rows.Count, 2)     ' Now customer and item rate
                                                                        
                                                                        ' Formula looks up item rate
    rngOutput.Formula2 = "=VLOOKUP(" & rngOutput.Cells(1, -14).Address(False, False) & ", " & wksDash.Name & "!" & rngCustomer.Address & ", 2, FALSE)"
    rngOutput.Copy                                                      ' Next statements copy then pastes as values the item rate
    rngOutput.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
   ...
    
    wksDash.Range("DateA").Resize(2, 1).ClearContents           ' Clears dashboard data
    wksDash.Range("FileName").Resize(3, 1).ClearContents
    wksDash.Range("Matches").Resize(10000, 3).ClearContents
    rngCustomer.Resize(1000, 2).ClearContents   'FAILS, rngCustomer is Nothing
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top