StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok here is an example. In this example I have directly taken the string which is mentioned in the question.</p> <p><strong>LOGIC</strong>:</p> <ol> <li>Split the cell addresses and store them in an array</li> <li>Remake the string, ensuring the length is not more than 240 (I have used 230. I am considering cells like <code>XFD1048576</code>)</li> <li>Once that limit is reached create a Named Range. I have used names like <code>MyRange1,MyRange2,MyRange3...</code>. One can use ever shorter names instead like <code>Rng1,Rng2...</code></li> <li>Repeat steps 2 and 3 till all named ranges are made.</li> <li>Simply use the named ranges in a formula</li> </ol> <p><strong>CODE</strong>:</p> <pre><code>Option Explicit Sub Sample() Dim MyAr() As String Dim strRng As String, strFormula As String, strTmp As String Dim i As Long, nmRngCount As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") '~~> Sample Cells strRng = "G2057,G2066,G2069,G2072,G2075,G2079,G2082,G2085,G2089,G2092,G2097,G2100,G2103,G2106,G2109,G2112,G2118,G2122,G2125,G2128,G2133,G2134,G2137,G2142," strRng = strRng & "G2145,G2152,G2155,G2159,G2162,G2165,G2170,G2173,G2176,G2182,G2188,G2191,G2195,G2200,G2203,G2207,G2210,G2213,G2220,G2224,G2227,G2232,G2236," strRng = strRng & "G2239,G2243,G2248,G2254,G2257,G2260,G2266,G2272,G2275,G2280,G2283,G2287,G2291,G2295,G2298,G2302,G2308,G2311,G2315,G2318,G2321,G2324,G2330," strRng = strRng & "G2333,G2337,G2340,G2345,G2348,G2353,G2356,G2360,G2363,G2390,G2393,G2397,G2404,G2407,G2410,G2413,G2422,G2425,G2433,G2437,G2441,G2448,G2449," strRng = strRng & "G2453,G2456,G2466,G2470,G2476,G2480,G2484,G2485,G2499,G2505,G2508,G2513,G2516,G2519,G2526,G2530,G2533,G2537,G2543,G2547,G2550,G2553,G2556," strRng = strRng & "G2557,G2560,G2563,G2566,G2580,G2581,G2586,G2589,G2601,G2605,G2611,G2614,G2619,G2625,G2628,G2633,G2638,G2646,G2649,G2653,G2659,G2670,G2673," strRng = strRng & "G2677,G2680,G2683,G2686,G2698,G2701,G2704,G2801,G2804,G2808,G2811,G2814,G2818,G2834,G2837,G2840,G2845,G2848,G2851,G2854,G2864,G2868,G2869," strRng = strRng & "G2882,G2885,G2888,G2892,G2896,G2900,G2901,G2904,G2909,G2912,G2915,G2919,G2923,G2926,G2930,G2933,G2936,G2946,G2952,G2956,G2959,G2964,G2967," strRng = strRng & "G2983,G2984,G2989,G2992,G2996,G3000,G3003,G3009,G3014,G3024,G3029,G3032,G3035,G3038,G3041,G3044,G3045,G3048,G3052,G3055,G3056,G3059,G3062," strRng = strRng & "G3081,G3084,G3088,G3093,G3096,G3099,G3100,G3103,G3107,G3110,G3113,G3119,G3124,G3127,G3130,G3135,G3138,G3141,G3142,G3143,G3146,G3147,G3150," strRng = strRng & "G3157,G3160,G3174,G3176,G3181,G3188,G3191,G3198,G3203,G3206,G3210" '~~> Split each cell and store it's address in an array MyAr = Split(strRng, ",") nmRngCount = 1 '~~> Loop through the array and rejoin the cell addresses For i = LBound(MyAr) To UBound(MyAr) strTmp = strTmp & "," & MyAr(i) '~~> 230 seems a pretty safe number even if I consider cells like XFD1048576) If Len(strTmp) > 230 Then '~~> Ignore the 1st Comma strTmp = Mid(strTmp, 2) '~~> Creat Names for the range ws.Range(strTmp).Name = "MyRange" & nmRngCount nmRngCount = nmRngCount + 1 strTmp = "" End If Next i '~~> Join the Names so that we get something like '~~> MyRange1,MyRange2,MyRange3,.... For i = 1 To (nmRngCount - 1) strFormula = strFormula & "," & "MyRange" & i Next '~~> Ignore the 1st Comma strFormula = Mid(strFormula, 2) '~~> Insert the final formula ws.Range("A1").Formula = "=SUM(" & strFormula & ")" End Sub </code></pre> <p><strong>SCREENSHOT</strong></p> <p><img src="https://i.stack.imgur.com/JoEB0.png" alt="enter image description here"></p> <p>So these 6 Named Ranges have some 240 cells covered.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload