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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| Public Sub Test()
Dim XlApp As Excel.Application
Dim XlWbk As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim FirstCell As Excel.Range
Dim LastCell As Excel.Range
Dim CellsDel As Excel.Range
Dim CellsDel2 As Excel.Range
Dim i As Integer
Set XlApp = Application.Application
Set XlWbk = XlApp.ActiveWorkbook
Set XLSheet = XlWbk.Worksheets("Feuil1")
XLSheet.Activate
Set FirstCell = XLSheet.Cells(3, 1)
Set LastCell = XLSheet.Cells(24, 5)
For i = LastCell.Row To FirstCell.Row Step -1
If Cells(i, 3).Text = "Client" Then
If CellsDel Is Nothing Then
Set CellsDel = XLSheet.Cells(i, 3)
Else
Set CellsDel = Union(CellsDel, XLSheet.Cells(i, 3))
If XLSheet.Cells(i - 1, 3).Text = "_t" Then
CellsDel.EntireRow.Delete Shift:=xlUp
Set LastCell = XLSheet.Cells.Range("A65536").End(xlUp)
' ne faudrait-il pas redimensionner la zone de travail (ReDim ?)
If XLSheet.Cells(i, 3).Text = "_t" Or _
XLSheet.Cells(i, 3) = "" Then
If CellsDel2 Is Nothing Then
Set CellsDel2 = XLSheet.Cells(i - 1, 3)
Else
Set CellsDel2 = Union(CellsDel2, XLSheet.Cells(i - 1, 3))
End If
'il manque aussi une condition pour prendre en compte le niveau du titre (2, 2.1, etc).
Else
Set CellsDel2 = Nothing
End If
End If
End If
End If
Next i
CellsDel.EntireRow.Delete Shift:=xlUp
CellsDel2.EntireRow.Delete Shift:=xlUp
End Sub |
Partager