Selecting non sequenced cells in a range

loudstil

New Member
Joined
Mar 1, 2010
Messages
6
Hi,
I'm trying to use the SUM function with non sequenced cell but the Range or any other function won't allow me to activate the selecting cells if there are more then 41 cell, I wrote somthing like so:

Code:
Sub Test()
Dim str AS String
Dim R AS Range

str = "A1,A3 ...... ,A57" 'this is only an example, assum that there are _
                                 more then 41 cells
Set R = Range(str) 'Here is the Problem, I get a Runtime error 1004 _
                          Application-defined or object-defined error
R.select
End Sub
But if the str input was a 41 or less Cells address string then it works

any help will be appreciated

Daniel
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi Daniel and welcome to the board.

You might as well populate the range address rather than use a string variable:

Set R = Range("A1,A3,...A57")
 

loudstil

New Member
Joined
Mar 1, 2010
Messages
6
Hi Jon
Thanks for the Reply.
I'm using the String because I wrote a Macro that needs to use the Sum function only to a certain cells and its more then 100 cells so in the macro I'm allways keeping those certain cells in an array and then take their address and put then in a string.
I havn't thought about another way of doing it and I'm open to another suggestions

Thanks, Daniel
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi Daniel

In theory what you are doing should work. I have even tested with your sample and provided each element is a valid cell address then it works.

But perhaps you can share the actual code that you are attempting to use? How do you determine which cells make up the wanted range?

BTW - rather than storing addresses in a string we can union ranges to a defined range object.

E.g:

Code:
Dim rngTable as Range
 
Set rngTable = Range("A1")
Set rngTable = Union(rngTable,Range("A3"))
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

If there is some logic that allows you to establish which cells are to be summed then it would make sense to utilise that also (ie via a SUMIF or SUMPRODUCT).

If for ex. you're summing every other row but all rows are numeric (ie SUM won't work) then you could use the contiguous range in a SUMPRODUCT but include based on Odd status of Row (via MOD)
 

loudstil

New Member
Joined
Mar 1, 2010
Messages
6
Hi Jon
I tried your Idea but it still do not work for a range larger than 43 cells, after a the 43 cell it won't add him to the range...
 

loudstil

New Member
Joined
Mar 1, 2010
Messages
6

ADVERTISEMENT

Well I kind of figure it out, the Range Method can only get a String up to 255 characters and my string is way larger then that
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi again Daniel

I really think you need to share your code and explain more clearly what you are attempting to do. That is, how do you determine which cells should make up the range.

Note DO's comment earlier, there are means for calculating only certain cells in a given range...

It seems that when you are using a string of range addresses you are confined to 42. You are confined to 42 because of the legnth of the string (255). But your union range can cope with many, many more ranges.

Take the following example:

Code:
[COLOR=blue]Sub[/COLOR] test()
 
[COLOR=blue]Dim[/COLOR] rngCell [COLOR=blue]As[/COLOR] Range
[COLOR=blue]Dim[/COLOR] lng [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] rngUnion [COLOR=blue]As[/COLOR] Range
 
[COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] Selection
    lng = lng + 1
    [COLOR=blue]If[/COLOR] lng [COLOR=blue]Mod[/COLOR] 2 = 0 [COLOR=blue]Then[/COLOR]
        [COLOR=blue]If[/COLOR] rngUnion [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
            [COLOR=blue]Set[/COLOR] rngUnion = rngCell
        [COLOR=blue]Else[/COLOR]
            [COLOR=blue]Set[/COLOR] rngUnion = Union(rngUnion, rngCell)
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]Next[/COLOR] rngCell
 
[COLOR=blue]Debug.Print[/COLOR] rngUnion.Count
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Here we dealing with a range object, not a string. Select a single column selection of e.g. 200 rows and run this macro, and it will tell you how many cells the range object is comprised of.

We can use this range in a calculation, e.g.:

Debug.Print Application.Sum(rngUnion)

So I suppose the point here is that whatever the logic is that you are using to determine which ranges apply, rather than storing the address to a string, maintain a range object and union each cell to that range object instead.
 
Last edited:

loudstil

New Member
Joined
Mar 1, 2010
Messages
6
Hi Jon
I used your idea above and It worked.
What I did was so, I saved all the addresses of the cells I counted in a string and then called to a function which get the string length and dividing it by 254, the max characters in a SUM formula, then I saved every substring in a strings array and I used the SUM forrmula for each array member and then I used the SUM formula for the whole Range of the Sums, Here is the Function:
Code:
Function GetStrings(ByRef s As String)
'Resolving the SUM formula limit issue
Dim i, j, length, Divider As Integer
Dim t(), s1 As String
Divider = 254
s1 = s
length = Len(s)
i = Round(length / Divider)
ReDim t(i + 1)
For j = 1 To i + 1
   If j = i + 1 Then
       t(j) = s1
       GoTo Con
   Else
    t(j) = Left(s1, Divider)
   End If
   Do While Not ChckString(t(j))
        Divider = Divider - 1
        t(j) = Left(s1, Divider)
        If ChckString(t(j)) Then GoTo Con
   Loop
Con:
      length = length - Divider
      If j <= i Then
        s1 = Right(s1, length)
        t(j) = Left(t(j), Len(t(j)) - 1)
      End If
      Cells(j, 4).Value = t(j)
      Cells(j, 2).Formula = "=Sum(" & t(j) & ")"
Next j
Cells(12, 2).Formula = "=Sum(" & Range(Cells(1, 2), Cells(11, 2)).Address & ")"
End Function
This Function help to explode the string excetlly in the ',' character
Code:
Function ChckString(ByVal Cs As String) As Boolean
'Checking
If Right(Cs, 1) <> "," Then
    ChckString = False
Else
    ChckString = True
End If
End Function
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi Daniel

What is the routine that builds the string? I still don't see any point in loading a string at all, and instead I would use sub routine / function to build a range object.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top