得到当前单元格区域以外的区域

leolee82

不知道有没有人有这个需求。以前尝试过，没写出来。今天突然有了灵感才写出来的，这个版本很容易看出思路，没有做过多的优化

1. Function GetRangeOutsideRange_(rng As Range) As Range
   
2. '返回rng以外的所有单元格区域
   
3. 
   
4. On Error Resume Next
   
5. 'Cells.ClearFormats
   
6. Dim r As Range, rngTemp As Range, ret As Range
   
7. rng.Application.ScreenUpdating = False
   
8. For Each r In rng.Areas
   
9.     r.EntireRow.Hidden = True
   
10.     Set rngTemp = rng.Application.Cells.SpecialCells(xlCellTypeVisible)
    
11.     r.EntireRow.Hidden = False
    
12.    
    
13.     r.EntireColumn.Hidden = True
    
14.     Set rngTemp = rng.Application.Union(rngTemp, rng.Application.Cells.SpecialCells(xlCellTypeVisible))
    
15.     r.EntireColumn.Hidden = False
    
16.    
    
17.     If ret Is Nothing Then
    
18.         Set ret = rngTemp
    
19.     Else
    
20.         Set ret = rng.Application.Intersect(ret, rngTemp)
    
21.     End If
    
22. Next
    
23. rng.Application.ScreenUpdating = True
    
24. Set GetRangeOutsideRange_ = ret
    
25. End Function
    

复制代码