excel vba 如何同时选择多个非连继单元格（个数由用户指定）

Question

在excel 中，请问如何实现在指定区域中选择指定个的多个非连继单元格？
比如说，我在A5：D10这个区域（我用Set sng=(range(cells(5,1),cells(10,4))定义了该区域），
要任意选择2个或者3个或者n个单元格合并其内容且存在指定变量中（如果是2个任意单元格，我用
nbr1=int((24*rnd)+1)
nbr2=int(24*rnd)+1)
cells(1,1)=sng(nbr1)&sng(nbr2)
如果是3个，我用
nbr1=int((24*rnd)+1)
nbr2=int(24*rnd)+1)
nbr3=int(24*rnd)+1)
cells(1,1)=sng(nbr1)&sng(nbr2)&sng(nbr3)
如果要取任意n个单元格呢？请问如何用循环语句来实现
cells(1,1)=sng(nbr1)&sng(nbr2)&sng(nbr3)...&sng(nbrN)?
）。

Answer 1

只要有规律可循，就可用循环语句 For ... Next，Do ... Loop 来实现

Answer 2

按住CTRIL键在用鼠标单击你想要选中的单元格！

Answer 3

1.
Sub selectCel() 'union count, not use range array
With ActiveSheet
Dim iRng As Range, uuRng As Range
Dim i%, n%, t%, o$

Set iRng = [a5:d20]
t = iRng.Count
iRng.Clear 'clear contents, formats

n = Application.InputBox("enter number", "random cell", 12, 500, 600)
'or use n = 12

'calculate
r = Int((t - 1) * Rnd + 1) '=Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Set uuRng = iRng(r) 'set first range in union
o = r 'record rnd number

Do
r = Int((t - 1) * Rnd + 1)
Set uuRng = Union(uuRng, iRng(r))
o = o & Chr(8) & r
Loop While uuRng.Count < n 'filter same me number/range

uuRng.Select
uuRng.Borders.ColorIndex = 45 'or Selection.Borders.ColorIndex = 45

MsgBox uuRng.Count & Chr(13) & o & Chr(13) & uuRng.Address, vbRetryCancel, "result"
[k1] = o 'o may has same number
[k3] = uuRng.Address

End With
End Sub

2.
Sub selectCel2() 'use string/range array, union not count
With ActiveSheet
Dim iRng As Range, cel() As Range, rndNumList() As String, rng As Variant, sRng As Range, uRng As Range, uuRng As Range
Dim i%, n%, t%, o$, a%, b%, c%, celAd$

Set iRng = [a5:d20]
iRng.Clear
t = iRng.Count
n = Application.InputBox("enter number", "random cell", 12, 500, 600) 'or test using n = 12

ReDim cel(n - 1) 'set range array collect range
ReDim rndNumList(n - 1) 'set string array record r

For i = LBound(cel) To UBound(cel)

If i = LBound(cel) Then
r = Int((t - 1) * Rnd + 1) '=Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Set cel(i) = iRng(r) 'Set sRng(i) = iRng(r)'better aviod use range to compare, use address
Set uuRng = iRng(r)
Else

line:
r = Int((t - 1) * Rnd + 1)
For e = LBound(cel) To i
If r = rndNumList(e) Then GoTo line: 'find same rnd number
Next e
Set cel(i) = iRng(r)
Set uuRng = Union(uuRng, iRng(r))
End If

rndNumList(i) = r 'record r

Next i

For d = LBound(rndNumList) To UBound(rndNumList)
rndNum = rndNum & "," & rndNumList(d) 'list r
Next d

'version 1---use cel() range array then union

Set uRng = cel(0)
For c = LBound(cel) + 1 To UBound(cel)
Set uRng = Union(uRng, cel(c))
Next c

uRng.Value = 1
'uRng.Activate

'or apply name to the range
'uRng.Name = "celSelect"
'[celSelect].Activate

'version 2---direct use union

uuRng.Borders.ColorIndex = 12 'set border color

'version 3---use cel() range array then get address then set range using final joint address

'celAd = cel(0).Address
'For a = LBound(cel) + 1 To UBound(cel)
'celAd = celAd & "," & cel(a).Address
'Next a

'Set sRng = Range(celAd)
'sRng.Activate

s
'version 4---use cel() range array, not activate, use value/code

'For b = LBound(cel) To UBound(cel)
'With cel(b)
'.Value = 1
'End With
'Next b

''or use
'For Each rng In cel
'rng.Value = 1
'Next

uRng.Activate 'version 1

MsgBox uRng.Count & Chr(13) & rndNum, vbRetryCancel, "result"
[k1] = uRng.Count
[k2] = rndNum
[k3] = uRng.Address
[k4] = uuRng.Address
[k5] = WorksheetFunction.CountIf(iRng, 1) 'count value 1 cell

End With
End Sub本回答被提问者采纳