Dynamic Named Range with Zero (0) Rows Triggers Error [1004 (Method 'Range' of object '_Global' failed)]

[Default300]

Hi. I'd love some assistance with this problem I'm having. Thanks to those who've helped in the past. (And if you're interested, yes it is the same project!)

For the short version of this post, skip to "PROBLEM"!

I'm assigning a Named Range to be the RowSource of a ComboBox Control on a UserForm.
The Named Range is Dynamic (and, if relevant, Global).
It is a single rectangular area of contiguous cells.
Its width is fixed to the number of columns in the table.
But its height is dynamic and varies from [NB] ZERO ROWS to the maximum Rows in table (about 800), depending upon the result of another operation.
The other operation is AdvancedFilter xlFilterCopy method, where the CopyToRange is the table I've been talking about (ie the future RowSource).
Incidentally, since I am useing xlFilterCopy (NOT xlFilterInPlace), all rows in all tables are visible.


PROBLEM
If the CopyToRange has ZERO ROWS, then when I try to assign it as a RowSource I receive:
Run-time error '1004':
Method 'Range' of object '_Global' failed

TROUBLESHOOTING
I have tried various ways to detect and circumvent the error (see code), but all of my attempts so far trigger the error themselves!

NAMED RANGES
I use very long descriptive names for my Named Ranges, to ensure that I or anyone coming after me can understand what they refer to.
I apologise if they are so long that it is hard to read the code.

There are just 2 Tables (Source and Destination).
SOURCE TABLE
Has 2 Named Ranges
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
All Records, Unfiltered
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
All Records, Unfiltered

DESTINATION TABLE
Has 4 Named Ranges
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
Filtered Records, (+ Blank Rows to bottom of table)
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
Filtered Records, (+ Blank Rows to bottom of table)

namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableInclHeader
Dynamic
1-801 Rows (Header Included In Range)
Filtered Records Only
namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader
Dynamic
0-800 Rows (Header Excluded From Range)
Filtered Records Only


IDEA & SOLUTION PREFERENCE
As you'll see from the code, I'm using "with header" for the AdvancedFilter operation, but "without header" for the RowSource.
I wonder whether Dynamic Range "with header" could be used to detect a Range with 1 Row, since 0 Rows gives an Error.

I would prefer NOT to Exit Sub in the case of 0 Rows.
I would like to assign a different single row RowSource with a message such as "(No Records)" instead.
Therefore, I would like to step over the erroneous RowSource assignment (with If, If Not, or Case Select perhaps).
I have not yet tried On Error statements (but see code).
I will do do if required, but would prefer to code for this specific condition (zero rows), especially as I want to assign the single row RowSource in that situation only.

CODE
This is the code for the AdvancedFilter operation, followed by the RowSource assignment.
The commented code below that shows some of the failed attempts so far.

[FONT=Courier New]Range("namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableInclHeader").AdvancedFilter _[/FONT]
[FONT=Courier New]   Action:=xlFilterCopy, _[/FONT]
[FONT=Courier New]   CriteriaRange:=Range("namListsCriteriaRange"), _[/FONT]
[FONT=Courier New]   CopyToRange:=Range("namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader"), _[/FONT]
[FONT=Courier New]   Unique:=False[/FONT]
 
[FONT=Courier New]'(i)    (ERROR occurs in NEXT LINE...)[/FONT]
 
[FONT=Courier New]Me.cboNavigateGotoClientSortedForename1Surname1Location1.RowSource = _[/FONT]
[FONT=Courier New]   "namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader"[/FONT]
 
[FONT=Courier New]Me.cboNavigateGotoClientSortedForename1Surname1Location1.Value = Null[/FONT]
 
[FONT=Courier New]'(i)    (Attempts to circumvent error, wrapped around error line...)[/FONT]
 
[FONT=Courier New]''If Not Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader") Is Nothing Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Not IsNull(Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader")) Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader").Rows.Count = 0 Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableInclHeader").Rows.Count > 1 Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Not Application.Intersect( _[/FONT]
[FONT=Courier New]   Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader"), _[/FONT]
[FONT=Courier New]   Range("namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader")) _[/FONT]
[FONT=Courier New]   Is Nothing Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Not Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader") Is Nothing Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]''If Not IsNull(Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader")) Then[/FONT]
 
[FONT=Courier New]''End If[/FONT]
 
[FONT=Courier New]'(i)    (I have not yet tried...)[/FONT]
[FONT=Courier New]''On Error Resume Next[/FONT]
[FONT=Courier New]''On Error Goto 0[/FONT]

NAMED RANGE REFERS TO FORMULAE

For those interested, the RefersTo Formulae for the Dynamic Ranges are:

namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableInclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,0,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1)+1,COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))

namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableExclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,1,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1),COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))


Thanks to Ozgrid for the HowTo: [ http://www.ozgrid.com/Excel/DynamicRanges.htm ]


Thank in advance!

 

[sous2817]

Shot in the dark (and not tested) but, couldn't you check and see if the row count for namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader = 0 and if it does set it to A1 (or some other acceptable range)

Something like:

If Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader").Rows.Count = 0 then 
Me.cboNavigateGotoClientSortedForename1Surname1Location1.RowSource = Sheet1.Range("A1")
else:
Me.cboNavigateGotoClientSortedForename1Surname1Location1.RowSource = _
   "namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader"

 

[Default300]

Re: Dynamic Named Range with Zero (0) Rows Triggers Error 1004 (Method 'Range' of object '_Global' failed)

Hi.

Thanks for taking the time to read my post and help me troubleshoot.

Unfortunately though, your suggestion is one of the ones I've tried already (third in list of commented out attempts).
I also tried [ If ... <>0 ] but no joy.

In case I wasn't clear, all of the attempts to avoid the error listed in the original post actually caused the same error!! (As far as I remember, the error was the same ie 1004.)

I think the tests are asking questions about something that doesn't exist (ie a zero dimensional range). That's why I was searching down the Is Nothing route. And (clutching at straws) tried IsNull, IsEmpty etc, which give Type Mismatch errors.

One option which I'm considering is as follows.

[FONT=Courier New]
Dim rngCheckForZeroRecords As Range

On Error Resume Next

Set [/FONT][FONT=Courier New]rngCheckForZeroRecords = Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader")

On Error Goto 0


If [/FONT][FONT=Courier New]rngCheckForZeroRecords Is Nothing Then

'Handle Zero Rows scenario

End If

[/FONT][FONT=Courier New]
If [/FONT][FONT=Courier New]Not rngCheckForZeroRecords Is Nothing Then

'Handle Not Zero Rows scenario

End If[/FONT]
[FONT=Courier New]

'Rest of code common to both scenarios

[/FONT]

(This was inspired by the following post on another forum that I came across when searching the web: [ http://www.ozgrid.com/forum/showpost.php?p=132422&postcount=15 ]. Credit to "Batman".)

I haven't tested it yet, but reckon it's next on the list.

It looks concise.

And I think it should work since what I'm testing is whether the range variable has changed from its default of Nothing, rather than directly testing the original (?untestable) zero-dimensional range.

The only drawback I see is having to load up to 8000 cells into a variable for a quick test and then discard the variable immediately after the test.

Perhaps I should just use the first row of my original CopyToRange, since xlFilterCopy will always start at the top row of the target range (even if just 1 record is returned).

Any other thoughts or better suggestions would be greatly appreciated.

 

[Default300]

Just to clarify the last thing I said, I meant better suggestions than my own suggestion, not better than the one by sous2817!

 

[Default300]

Re: Dynamic Named Range with Zero (0) Rows Triggers Error 1004 (Method 'Range' of object '_Global' failed)

SUCCESS ?

Ok. For anyone interested, initial testing suggests that the above code seems to work.

It also seems to work with a modification to examine just the first row (see code below).

Set rngCheckForZeroRecords = _
    Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader").Rows(1)

In case anyone is following in my footsteps, I'll mention that in the scenario where the range variable Is NOT Nothing, it needs to be reset to Nothing after the check.

I added the following line of code to do that.

Set rngCheckForZeroRecords = Nothing

NEW PROBLEM!

Now, I'm having a little difficulty working out what to set the RowSource to when zero records are returned.

The single row table with "(No Records)" in all visible columns of the multi-column ComboBox is fine visually, but I'm stumped as to what to put in the BoundColumn Cell for that "dummy" row.

The real records have a unique number 1 to 800 in the BoundColumn Cell, which is used as an Index for the Record, and used for a ScrollIntoView style function on a worksheet.

Perhaps I'll set it to 0, and then prior to executing the ScrollIntoView style function, check for ComboBox Value =0, and if so Exit Sub.

Again, any ideas welcome!

 

[Fazza]

Re: Dynamic Named Range with Zero (0) Rows Triggers Error 1004 (Method 'Range' of object '_Global' failed)

Hi

if no records, set .RowSource = vbNullString

HTH, F

 

[Default300]

Re: Dynamic Named Range with Zero (0) Rows Triggers Error 1004 (Method 'Range' of object '_Global' failed)

@Fazza
Thanks. That will be a very useful method in the future (for another time/situation). Handy to know. Otherwise, I would probably fiddle around with trying to set RowSource to Null, Nothing, 0 etc., forgetting that RowSource needs to be a String, not a Range. Also, intuitively, I would have expected having no RowSource (ie vbNullString) to cause an error, so probably would not even have tried to go that route. Cheers.

In this case, I have opted for the single row RowSource with text "No Records" in each Field including the TextColumn, and "0" (zero) in the BoundColumn.

(I'm not sure if the "0" is relevant, because I think my code will ensure that it is not used anyway, but in case it is being processed accidentally, I thought it better be a number, as in original RowSource, but a number which would never occur in the original RowSource ie not 1 to 800).

When there are zero records in the original RowSource, the RowSource is switched to the single row substitute Named Range. Then, with UserForm Events temporarily disabled, the ComboBox Value is set to "0" which displays the TextColumn "No Records" in the ComboBox Text property. Then it sets RowSource.Enabled = False, which greys out the "No Records" message, and prevents the user from clicking the control.

This seems to work. Having said that, you'll see from other posts that I'm an untrained amateur enthusiast, so I may be contravening some common principle(s) of best practice in coding. I'm always very happy to be informed of them by the way!