1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| Option Explicit
Type propert
aeraByContigu(1 To 1000) As Range
aeraAllSUsedcells As Range
aeraByCuRegion(1 To 1000) As Range
End Type
Public maFeuilleUsedRange As propert
Function maFeuilleaeracount(Optional splage As Range) As Long
Dim RgAdresse, dicoRange, nbe As Integer, F As Range, C As Range, i As Long, alls As String
Dim OK As Boolean, plage As Range, region As Range, dicoexist As Boolean, elem, rngUsed, Nbcellformula As Boolean
OK = splage Is Nothing = False: Set plage = IIf(OK, splage, ActiveSheet.UsedRange)
With plage
Set rngUsed = .SpecialCells(xlCellTypeConstants)
If IsNull(plage.HasFormula) Or plage.HasFormula = True Then Set rngUsed = Union(rngUsed, .SpecialCells(xlCellTypeFormulas))
End With
Set plage = rngUsed
Set dicoRange = CreateObject("Scripting.Dictionary")
Set maFeuilleUsedRange.aeraAllSUsedcells = plage
RgAdresse = Split(plage.Address, ",")
For i = 0 To UBound(RgAdresse)
Set region = Range(RgAdresse(i), RgAdresse(i)).CurrentRegion
dicoexist = dicoRange.exists(region.Address) = True
dicoRange(region.Address) = IIf(dicoexist, dicoRange(region.Address) & RgAdresse(i) & ",", "(" & RgAdresse(i) & ",")
Next
For Each elem In dicoRange
dicoRange(elem) = Replace(dicoRange(elem) & ")", ",)", ")")
nbe = nbe + 1
Set maFeuilleUsedRange.aeraByContigu(nbe) = Range(dicoRange(elem))
Set maFeuilleUsedRange.aeraByCuRegion(nbe) = Range(elem)
alls = alls & dicoRange(elem)
Next
maFeuilleaeracount = dicoRange.count
End Function |
Partager