我試圖設定以下范圍：
Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102")
我嘗試使用 For 回圈和 迭代地構建它Application.Union，而不是手動輸入 Range，因為我必須構建許多類似的范圍，而且我需要永遠輸入它們，所以我使用了以下代碼：
Sub Build_Range()
Dim FirstParamCol, ParamCells As Range
Dim i As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False 'avoid infinite loop if any target cell is changed
Set ParamCells = Range("F3:F102") 'There 20 different "Parameter" columns, the first one being Column F (column 6)
'Note: After column F (6), the other 19 columns are 4 columns apart each (j/10, N/14, ...)
'so I looped for i = 1 to 19 and used the formula column = 6 (column F) i * 4:
For i = 1 To 19 'There are other 19 "Parameter" columns
Set ParamCells = Application.union(ParamCells, Range(ActiveSheet.Cells(3, 6 4 * i), ActiveSheet.Cells(102, 6 4 * i)))
Next i
MsgBox ParamCells.Address 'TODO: For Debugging only
Range("B103").Value = ParamCells.Address
Exitsub:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
運行上述代碼后，我得到以下范圍，其中缺少最后一列("CD3:CD102")：
Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102")
我嘗試增加 For 回圈中的最后一個 i 值，但我一直得到與上面相同的范圍。
我什至嘗試使用以下代碼手動設定范圍，但仍然得到與上述相同的范圍，再次缺少最后一列：
Sub Build_Range_2()
Dim ParamCells As Range
Set ParamCells = Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102")
MsgBox ParamCells.Address
Range("B103").Value = ParamCells.Address
End Sub
在我手動設定 Rage 并且仍然 excel/vba 更改范圍后，我感到很沮喪。我搜索了它是否是 Range 的最大大小的某種限制或類似的東西，但我找不到任何東西。
澄清更新：我在 MsgBox 和 Range("B103").Value 上列印構建的 Range，僅用于除錯目的（我不需要顯示 Range，而是使用它/使用它）。
uj5u.com熱心網友回復：
首先，好訊息是您構建該范圍的代碼可以正常作業！
插入 aParamCells.Select并檢查它選擇的內容。甚至"CD3:CD102"沒有顯示在地址中ParamCells.Address它是范圍的一部分ParamCells。
問題是.Address限制為 255 個字符。
你可以很容易地檢查，如果你比較
Debug.Print ParamCells.Address
Debug.Print ParamCells.Address(False, False)
$F$3:$F$102,$J$3:$J$102,$N$3:$N$102,$R$3:$R$102,$V$3:$V$102,$Z$3:$Z$102,$AD$3:$AD$102,$AH$3:$AH$102,$AL$3:$AL$102,$AP$3:$AP$102,$AT$3:$AT$102,$AX$3:$AX$102,$BB$3:$BB$102,$BF$3:$BF$102,$BJ$3:$BJ$102,$BN$3:$BN$102,$BR$3:$BR$102,$BV$3:$BV$102,$BZ$3:$BZ$102
F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102
第一個有 253 個字符，添加CD3:CD102到字串中會超過 255，因此它被截斷。第二個沒有絕對地址，因此它更短，所以最后你可以看到…,BZ3:BZ102,CD3:CD102它CD3:CD102實際上在范圍內。
因此，無論何時使用ParamCells它都可以在整個范圍內作業，無論ParamCells.Address被截斷為什么。
所以問題是你需要顯示地址嗎？然后你需要一些解決方法（取決于你的實際目標是什么）。或者它只是為了除錯你的代碼然后ParamCells.Select用來檢查它。
如果要輸出ParamCellsvor 驗證的實際地址，可以通過回圈ParamCells.Areas.
Dim AddrOfParamCells As String
Dim Area As Range
For Each Area In ParamCells.Areas
AddrOfParamCells = IIf(AddrOfParamCells <> vbNullString, AddrOfParamCells & ",", vbNullString) & Area.Address
Next Area
Debug.Print AddrOfParamCells
請注意，此地址只能用于驗證，但您不能使用它來構建范圍，Set TestRange = Range(AddrOfParamCells)因為它超過 255 個字符。
uj5u.com熱心網友回復：
.Address 限制為 255 個字符...但您可以使用字串來處理。
Sub Build_Range()
Dim FirstParamCol As Range
Dim ParamCells As Range
Dim i As Integer
Dim StrRange As String 'String to store the address of ParamCells
Application.ScreenUpdating = False
Application.EnableEvents = False
'Is not a good practice to hard code... but it is your code, and
'You will debug this... in the future!
Set ParamCells = Range("F3:F102")
StrRange = ParamCells.Address(1, 1, xlA1, 0, 0)
'To find some help in .Address:
'https://docs.microsoft.com/en-us/office/vba/api/excel.range.address
For i = 1 To 19
'Here you store the address.
'Again, don't like to hard code... but the & "," & in this point it is necessary
StrRange = StrRange & "," & Range(ActiveSheet.Cells(3, 6 4 * i), ActiveSheet.Cells(102, 6 4 * i)).Address(1, 1, xlA1, 0, 0)
'I Keep youy var, if you want to do something with the range.
'You can not print the full address in the immediate window, but
'You can still use the full range.
Set ParamCells = Application.Union(ParamCells, Range(ActiveSheet.Cells(3, 6 4 * i), ActiveSheet.Cells(102, 6 4 * i)))
Next i
'Just to check if is working.
'ParamCells.Select
MsgBox ParamCells.Address 'TODO: For Debugging only
'No use this
'Range("B103").Value = ParamCells.Address
'Use this:
Range("B103").Value = ParamCells.Address
Exitsub:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/456769.html