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