Excel - Is there a limit to non-contiguous cells that can be selected? 3

[srogers]

Using Excel 2000:
As I understand it I can not select cells across several worksheets at once and give them one range name and expect to be able to use it. I read that is not supported?

So I thought I'd try one worksheet at a time. I selected 164 non-contiguous cells and gave them a range name. Then I tried to clearcontents. The last 48 cells did not clear. So I was wondering if there is a limit on the number of non-contiguous cells I can range name?

When I go to Insert, Name, define, and count the characters in the Refers To: box, I count 134 characters. Not sure if that has anything to do with it. I searched in help and in keywords here but didn't find anything.

Does anyone know?
Thanks for your time.

 

[Dreamboat]

>>if there is a limit on the number of non-contiguous cells I can range name

Not to my knowledge.

Anne Troy

http://www.VBAExpress.com

 

[DaleWatson123321]

Hi srogers,

I played with non-contiguous range names, and found it was more trouble than what it was worth - to create individual names.

If you use a naming convention - e.g. i_1, i_2, i_3, etc, it should help. First the names are short, so requires less typing. Secondly, by using sequential numbers, one can easily copy and paste the VBA code and then only change each of the numbers.

An "added bonus"... is that using this method, you CAN include range names on ANY sheet in the workbook.

Here's an example...

Sub Clear_Input()
[i_1].ClearContents
[i_2].ClearContents
[i_3].ClearContents
[i_4].ClearContents
End Sub

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Slight correction (to correct possible misunderstanding)...

Instead of...

I played with non-contiguous range names, and found it was more trouble than what it was worth - to create individual names.

I should have said...

I played with non-contiguous range names, and found it was more trouble than what it was worth. And found it preferable to take the time to create individual range names.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

With Excel 97, I was able to name a range with 149 areas, but naming the range fails when there are 150 areas.

There appears to be no practical limit to the number of areas in a range (using VBA to Union the areas in a loop - there is a maximum of 30 arguments in the Union operation). The problem arises only when you try to name it.

 

[srogers]

Oops - the 5th try was the charm I guess - now they are deleting. Not sure what I did.
But I will still be looking for the best way to clear all these areas on each of the 13 sheets.

Dale, when you said "An "added bonus"... is that using this method, you CAN include range names on ANY sheet in the workbook." Did you mean I can re-use the names on the other sheets?

I was thinking I might have to use a loop to go through all the sheets - but maybe there is a more efficient way.
Gotta leave the office just now.
Thanks for your help - I'll check back and try this later.

 

[luceze]

I don't know if this even applies. But using VBA you can only select 46 non-contiguous rows using non absolute range addresses. I've never tested on individual cells.

 

[Zathras]

Hmmm... luceze, where did you get the number 46? I was able to select 149 non-contiguous rows with VBA. Same limit as naming. (Excel 97).

 

[luceze]

Zathras,
Mabye you can see why I can't get more that 46. Here's my code. Excel 2002

Sub SelectUpTo46NonContiguousRows()
On Error GoTo errorX
Application.ScreenUpdating = False
Selection.Name = "DataRange"
Range("a1&quot.Select
Dim cell
Dim strCollect As String
Dim criteria As String
stringcollect = Empty
criteria = InputBox("Enter Criteria to select.", "Select Rows&quot
For Each cell In Range("DataRange&quot
cell.Activate
If ActiveCell = criteria Then
If strCollect = Empty Then
strCollect = ActiveCell.EntireRow.Address(False, False)
Else
strCollect = strCollect & "," & ActiveCell.EntireRow.Address(False, False)
End If
End If
Next

Range(strCollect).Activate
Application.ScreenUpdating = True
Exit Sub
errorX:
If Err.Number = 1004 Then
MsgBox "There are more than 46 rows that meet your criteria."
Else
MsgBox Err.Description
End If
Application.ScreenUpdating = True
End Sub

 

[Zathras]

Here is your function, slightly re-written to use ranges directly, rather than working with strings and names... I was able to get up to having several hundred rows selected when I quit testing. I don't know what the limit actually is.

One bit of advice: By putting [blue]

 Option Explicit

[/color] as the first line in the module, I was able to detect your typing mistake where you were referencing a variant "stringcollect" that wasn't declared and wasn't being used. (If you tick "Require Variable Declaration" in the Editor Options, it will be put there for you automatically.)
[blue]

Option Explicit

Sub FindAndSelectRows()
Dim RangeToSelect As Range
Dim c As Range
Dim criteria As String
  
  On Error GoTo errorX
  Application.ScreenUpdating = False
  criteria = InputBox("Enter Criteria to select.", "Select Rows")
  If criteria <> &quot;&quot; Then
    For Each c In Selection
      If c.Value = criteria Then
        If RangeToSelect Is Nothing Then
          Set RangeToSelect = c.EntireRow
        Else
          Set RangeToSelect = Union(RangeToSelect, c.EntireRow)
        End If
      End If
    Next c
    If RangeToSelect Is Nothing Then
      MsgBox &quot;No rows found to match the specified criteria&quot;
    Else
      RangeToSelect.Select
      Set RangeToSelect = Nothing
    End If
  End If
  Application.ScreenUpdating = True
  Exit Sub
  
errorX:
  If Err.Number = 1004 Then
    MsgBox &quot;There are too many rows that meet your criteria.&quot;
  Else
    MsgBox Err.Description
  End If
  Application.ScreenUpdating = True
End Sub

[/color]
I also added code to handle the cases where the user cancels out of the input box and where no rows match the selection criteria.

 

[luceze]

Very Cool! I learn something new all the time in this forum. Do you know why my original code was not able to select more than 46 rows?

 

[DaleWatson123321]

Hi srogers,

First, "thanks" for the STAR - it's appreciated ;-)

To answer your questions...

1) Dale, when you said "An "added bonus"... is that using this method, you CAN include range names on ANY sheet in the workbook." Did you mean I can re-use the names on the other sheets?

Answer: By referring to "re-use the names on the other sheets"... If you mean that you want to use "existing" names in your VBA code to clear the contents of those cells, you can do so.

If by "re-use", you mean you want to "re-assign" existing names to other locations, you can do this also. But in such a case, the implication is that you no longer require names for those particular existing locations. It's also IMPORTANT to note that one should NOT re-assign an existing range name IF the range name is referened by a formula. If you do re-assign such a range name, it will cause the formula to reference the NEW location to which you assign the range name.

2) I was thinking I might have to use a loop to go through all the sheets - but maybe there is a more efficient way.

Answer: No, you do NOT have to use a loop to go through all the sheets.

In the example I gave...

Sub Clear_Input()
[i_1].ClearContents
[i_2].ClearContents
[i_3].ClearContents
[i_4].ClearContents
...etc,
...etc,
End Sub

...any of the example range names could be ANYWHERE within the workbook - on ANY sheet. And as per the example, there is no requirement to reference the sheet name when you use a range name.

I hope this helps

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[srogers]

I'm just getting back to this today but I still have problems with just 44 areas on one sheet.

I select them all using the CTRL key and then in the Name box I type in my name - in this case Period2 and hit Enter.
Everything to that point appears fine.

Then I run:
Sub MakeDeletions()
[Period2].ClearContents
End Sub

31 of the areas clear
13 of the areas don't

Dale, I was hoping I could give all the areas on one sheet the same name (since I have 13 sheets). But if this is the kind of "trouble" you are talking about I will try giving 22 areas on sheet 1 one name and the second 22 areas a different name.

Thanks for your tip on a better way to use clearcontents because I was making a reference to the sheet and the range before .clearcontents.

Thank you -
Sharon