Zero Length Dynamic Range

G'day there One & All,

Stumped again, so here I am once more =). I have a number of
dynamic ranges to handle various lists in my project. I used 2 ranges on
one list to overcome a problem I couldn't find another way around. My
list has entries such as:

Sections <-- Heading
Unknown
Alpha
Bravo
Charlie
etc...

The entries are for testing only, as they will be set by the clients
once this is all sorted out. The only constant entries are the Heading,
and the "Unknown" entry. This doesn't designate an unknown quantity, but
is the string "Unknown". There will be no other entries when deployed.
The users will enter them via a form as required.

I have a dynamic range "Sections" of all entries including "Unknown". A
second one, "srtSections" is for everything under "Unknown" - Alpha -
Charlie in this case. That way my forms ListBox has a RowSource of
"Sections" which includes everything, but when the user adds or deletes
an entry I use "srtSections" and sort alphabetically. This leaves
"Unknown" on top of the ListBox at all times. I use ListBox.Listcount to
ensure that the last entry can't be removed.

All was working fine, until I tested to see how it all went & realised
that although I couldn't delete "Unknown" (correct behaviour), once I
got down to only that entry then "srtSections" was a range of zero
entries and my attempts to reference it raised Global Range errors.

The code is here:

Private Sub CommandButton6_Click()
' Add Section
' Set listbox rowsource to null preventing interference with dynamic
' range calculations
Me.ListBox1.RowSource = ""
' Set range variable to address of dynamic range after "Unknown"
' entry
If Application.WorksheetFunction.IsError(Range
("srtSections").Cells.Count) Then
dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
Else
Set gTgt = Range("srtSections")
' Add value onto bottom of list
Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
End If
' Sort list in alphabetical order
Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
Order1:=xlAscending
' Set listbox rowsource to full list of data as set by dynamic range
' including "Unknown" entry
Me.ListBox1.RowSource = "Data!Sections"

End Sub

I'm trying to add a member to my list, but the "srtSections" reference
has me stuck. How can I test for a range of zero cells without
generating an error? The "IsError" function I used doesn't work as the
error stops everything before I can test for it. Any ideas anyone?

See ya, thanks for thinking about it
Ken McLennan
Qld, Australia

If gTgt is declared as a global variable and is of type range, then delete
the Dim statement below.
If it is a global variable, but not of type range, then you will need to use
another local variable which is dim'd as range to do the test.

Private Sub CommandButton6_Click()
Dim gTgt as Range
' Add Section
' Set listbox rowsource to null preventing interference with dynamic
' range calculations
Me.ListBox1.RowSource = ""
' Set range variable to address of dynamic range after "Unknown"
' entry
On Error Resume Next
set gTgt = Range("srtSections")
On Error goto 0
If rng is nothing Then
dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
Else
' Set gTgt = Range("srtSections")
' Add value onto bottom of list
Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
End If
' Sort list in alphabetical order
Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
Order1:=xlAscending
' Set listbox rowsource to full list of data as set by dynamic range
' including "Unknown" entry
Me.ListBox1.RowSource = "Data!Sections"

End Sub


--
Regards,
Tom Ogilvy

"Ken McLennan" <[email protected]> wrote in message
news:[email protected]...
> G'day there One & All,
>
> Stumped again, so here I am once more =). I have a number of
> dynamic ranges to handle various lists in my project. I used 2 ranges on
> one list to overcome a problem I couldn't find another way around. My
> list has entries such as:
>
> Sections <-- Heading
> Unknown
> Alpha
> Bravo
> Charlie
> etc...
>
> The entries are for testing only, as they will be set by the clients
> once this is all sorted out. The only constant entries are the Heading,
> and the "Unknown" entry. This doesn't designate an unknown quantity, but
> is the string "Unknown". There will be no other entries when deployed.
> The users will enter them via a form as required.
>
> I have a dynamic range "Sections" of all entries including "Unknown". A
> second one, "srtSections" is for everything under "Unknown" - Alpha -
> Charlie in this case. That way my forms ListBox has a RowSource of
> "Sections" which includes everything, but when the user adds or deletes
> an entry I use "srtSections" and sort alphabetically. This leaves
> "Unknown" on top of the ListBox at all times. I use ListBox.Listcount to
> ensure that the last entry can't be removed.
>
> All was working fine, until I tested to see how it all went & realised
> that although I couldn't delete "Unknown" (correct behaviour), once I
> got down to only that entry then "srtSections" was a range of zero
> entries and my attempts to reference it raised Global Range errors.
>
> The code is here:
>
> Private Sub CommandButton6_Click()
> ' Add Section
> ' Set listbox rowsource to null preventing interference with dynamic
> ' range calculations
> Me.ListBox1.RowSource = ""
> ' Set range variable to address of dynamic range after "Unknown"
> ' entry
> If Application.WorksheetFunction.IsError(Range
> ("srtSections").Cells.Count) Then
> dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
> Else
> Set gTgt = Range("srtSections")
> ' Add value onto bottom of list
> Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
> End If
> ' Sort list in alphabetical order
> Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
> Order1:=xlAscending
> ' Set listbox rowsource to full list of data as set by dynamic range
> ' including "Unknown" entry
> Me.ListBox1.RowSource = "Data!Sections"
>
> End Sub
>
> I'm trying to add a member to my list, but the "srtSections" reference
> has me stuck. How can I test for a range of zero cells without
> generating an error? The "IsError" function I used doesn't work as the
> error stops everything before I can test for it. Any ideas anyone?
>
> See ya, thanks for thinking about it
> Ken McLennan
> Qld, Australia

G'day there Tom,

> If gTgt is declared as a global variable and is of type range, then delete
> the Dim statement below.

Yes, it's a global variable of type range. I got that bit right =)

> Private Sub CommandButton6_Click()
....
> On Error Resume Next
> set gTgt = Range("srtSections")
> On Error goto 0
> If rng is nothing Then
> dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
....
> End Sub

..."is nothing"!!! It's so simple once you point out where I
went wrong <g>.

Thanks very much for that. I've not put it to work as yet, as my
wife won't let me play on my 'pooter until we've done the grocery
shopping ...dammit!! (She has some strange idea that groceries don't
just magically appear in the cupboards when you need them).

See ya, and thanks once again,
Ken McLennan
Qld, Australia