Determine Cell Range: Excel 2000 1

[mpezeur]

Is there a way to determine what range a particular cell (or group of cells) is in, using VB, and Excel 2000.
For example, if range A1:F5 is named "First",

is there a way of determining whether cell C3 is part of this range?

 

[Zathras]

Try this:

Sub demo()
  If Intersect(Selection, Range("First")) Is Nothing Then
    MsgBox "Not in the range"
  Else
    MsgBox "In range"
  End If
End Sub

You can assign a short-cut key and then select various cells inside and outside of the range, run the macro and see what happens.

To assign a short-cut key: From Excel, Tools/Macro/Macros..., Click Options..., and assign a key.

 

[mpezeur]

Unfortunately, I can't use shortcut keys. I'm converting a spreadsheet from Lotus1-2-3 to Excel, trying to keep the same functionality as the original.
To go more into depth of what I'm trying to do:

Have a spreadsheet of different corporate locations across the US, and the listing of areas are in a pre-defined order which I'm not allowed to re-sort. Those locations are further broken down into groups, but the locations in those groups are spread out amongst the selections:

example:
rows 1-4,10 may be in Group A
rows 5-8,12-14 in Group B
rows 9,15 in grp C

The kind of information maintained for each location, may need to spill over onto a 2nd line, therefore there is an "add line" feature that will add a row directly underneath the location in question. Therefore, taking the example above, if row 3 was Topeka, KS, and I added a line underneath this, so that row 4 also was Topeka, KS, group A would then consist of rows 1-5,11. I was hoping this would be a dynamic change. I am using the following to add the line:

ActiveCell.Offset(1).EntireRow.Insert

and then copying the location name from the "activecell" to the offset cell.

For some reason, excel doesn't automatically pick up the new row added, and therefore doesn't automatically adjust the range for each group.
Therefore, when adding a new line, I need to determine, from which cell/row is current, which range name I'm in, add the row, and then recreate the range name to take into account the new range.

 

[Zathras]

Assigning a short-cut key was only to make the demo/test easier. It is not part of the final solution.

The main difficulty in your situation is the disjointed (aka nonadjacent selections) ranges. The solution would be rather easy if you had only simple ranges. (In fact, I had one all worked up until I took a closer look at your examples.)

If it's not possible to change the range names to something like GroupADetail, GroupASummary, GroupBDetail, GroupBSummary, etc. then can you at least guarantee that there will only be two selections per range? That at least would be easier than having to cope with a variable number of selections.

 

[Zathras]

Rethinking with multiple range segments actually resulted in simpler code than I started out with. (Life's funny that way.)

Here is the code I think you want to use:

Option Explicit

Sub InsertARow()
Dim sName As String
Dim rRange As Range
Dim sInsertAddress As String
  If GetCurrentRangeData(sName, rRange) Then

    ' In a range -- capture next row as range for union

[/code]
sInsertAddress = Intersect(ActiveCell.EntireRow, rRange).Offset(1, 0).Address[/code]

    ' Do insert

[/code]
ActiveCell.Offset(1).EntireRow.Insert[/code]

    ' Change definition of range

[/code]
Names(sName).Delete
Names.Add sName, Union(rRange, Range(sInsertAddress))
Set rRange = Nothing
Else[/code]

    ' Not in a range - just insert

[/code]
ActiveCell.Offset(1).EntireRow.Insert
End If
End Sub

Function GetCurrentRangeData(AName As String, ARange As Range) As Boolean
Dim i As Integer
Dim sRangeName As String
Dim r As Range
If TypeName(Selection) = "Range" Then[/code]

    ' Find where the current selection is in a named range...

[/code]
For i = 1 To Names.Count
Set r = Names(i).RefersToRange
If Not Intersect(r, Selection) Is Nothing Then[/code]

        '... Found it

[/code]
AName = Names(i).Name
Set ARange = r
Set r = Nothing
GetCurrentRangeData = True
Exit For
End If
Next i
Set r = Nothing
End If
End Function
[/code]