|
本帖最后由 excel白兔 于 2012-8-6 16:20 编辑
Private Sub CommandButton1_Click()
Dim a, c As Range
For Each a In Range("A1:if500")
If a.Interior.ColorIndex <> xlNone Then
If c Is Nothing Then
Set c = a
Else
Set c = Union(c, a)
End If
End If
Next
If Not c Is Nothing Then
c.Select
End If
End Sub
这个代码怎么能优化下呢 因为范围是在很大 运算起来太慢了15分钟都没弄出来
这个是 选中代色的单元格代码
本帖最后由 mxg825 于 2012-8-7 16:20 编辑
’搞定 用时 20秒 - Private Sub CommandButton1_Click()
- Dim MyAdd As String, TmpAdd As String
- Dim C As Range, Ran As Range, T As Single
- Dim Mrow%, Mcol%
- T = Timer
- For Each Ran In Range("A1:IF500")
- If Ran.Interior.ColorIndex <> xlNone Then
- TmpAdd = MyAdd
- MyAdd = MyAdd & Ran.Address(0, 0) & ","
- If Len(MyAdd) > 255 Then
- MyAdd = TmpAdd
- Call UinonRan(C, MyAdd)
- MyAdd = Ran.Address(0, 0) & ","
- End If
- End If
- Next
- If Len(MyAdd) > 0 Then Call UinonRan(C, MyAdd)
- If Not C Is Nothing Then
- C.Select
- End If
- MsgBox Timer - T
- End Sub
- Sub UinonRan(ByRef RanA As Range, RanAdd As String)
- RanAdd = Left(RanAdd, Len(RanAdd) - 1)
- If RanA Is Nothing Then
- Set RanA = Range(RanAdd)
- Else
- Set RanA = Union(RanA, Range(RanAdd))
- End If
- End Sub
复制代码
|
|