vba - 选择从 3 到 305 的所有奇数行

Question

我有一个听起来很简单的任务，但它给我带来了一些麻烦。
我需要做的只是对从 3 到 305 的所有奇数行进行多项选择(因此同时选择行 3, 5, 7, 9, ... 303, 305 )。

多选行时录制宏3和 5 ，我看到生成的代码如下:

Range("3:3,5:5").Select

从上面开始，我写了以下内容:

Dim strRange As String, j As Integer
strRange = "3:3,"
For j = 5 To 303 Step 2
   strRange = strRange & j & ":" & j & ","
Next j
strRange = strRange + "305:305"

以上 strRange将导致以下结果:

Debug.Print strRange
>>> 3:3,5:5,7:7,9:9,11:11,13:13,15:15,17:17,19:19,21:21,23:23,25:25,27:27,29:29,31:31,33:33,35:35,37:37,39:39,41:41,43:43,45:45,47:47,49:49,51:51,53:53,55:55,57:57,59:59,61:61,63:63,65:65,67:67,69:69,71:71,73:73,75:75,77:77,79:79,81:81,83:83,85:85,87:87,89:89,91:91,93:93,95:95,97:97,99:99,101:101,103:103,105:105,107:107,109:109,111:111,113:113,115:115,117:117,119:119,121:121,123:123,125:125,127:127,129:129,131:131,133:133,135:135,137:137,139:139,141:141,143:143,145:145,147:147,149:149,151:151,153:153,155:155,157:157,159:159,161:161,163:163,165:165,167:167,169:169,171:171,173:173,175:175,177:177,179:179,181:181,183:183,185:185,187:187,189:189,191:191,193:193,195:195,197:197,199:199,201:201,203:203,205:205,207:207,209:209,211:211,213:213,215:215,217:217,219:219,221:221,223:223,225:225,227:227,229:229,231:231,233:233,235:235,237:237,239:239,241:241,243:243,245:245,247:247,249:249,251:251,253:253,255:255,257:257,259:259,261:261,263:263,265:265,267:267,269:269,271:271,273:273,275:275,277:277,279:279,281:281,283:283,285:285,287:287,289:289,291:291,293:293,295:295,297:297,299:299,301:301,303:303,305:305

...看起来是正确的。但是，当我尝试这样做时:

Range(strRange).Select '<-- Note: workbook and worksheet are correctly referenced

...我得到一个 Run-time error 1004: Method 'Range' of object '_Global' failed.为什么会这样？我可以传递给 Range 的字符串是否有最大长度？我在网上找不到的类(class)？如果是这样，我将如何进行此多项选择？

Answer 1

试试这个:

Sub OddPicker()
   Dim rng As Range, i As Long

   For i = 3 To 305 Step 2
      If rng Is Nothing Then
         Set rng = Cells(i, 1)
      Else
         Set rng = Union(rng, Cells(i, 1))
      End If
   Next i

   rng.EntireRow.Select
End Sub

我找不到 Range() 的字符串限制；我看这里:

Excel Specifications and Limits

编辑#1:

我跑了这个:

Sub flskdjfs()
   Dim strRange As String, j As Integer
   strRange = "3:3,"
   Dim r As Range
   On Error GoTo whatis

   For j = 5 To 303 Step 2
      strRange = strRange & j & ":" & j & ","
      Set r = Range(Mid(strRange, 1, Len(strRange) - 1))
   Next j
   strRange = strRange + "305:305"

whatis:
   MsgBox j & vbCrLf & Len(strRange)
End Sub

并得到了这个 MsgBox 结果:



所以我假设在 256 个字符之后 Range() 变得不稳定。