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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| Option Explicit
Dim PrevLastRow As Integer, PrevLastColumn As Integer
Dim insertOnGoing As Boolean
Sub AddRowsOrColums()
' AJOUTER DES LIGNES OU DES COLONNES ENTIERES AVEC LES FORMULES SANS LES DONNEES
Dim nb As Integer, i As Integer
' ajouter des lignes
If Selection.Columns.Count = Columns.Count Then
'MsgBox "insert row"
nb = Selection.Rows.Count
ActiveCell.EntireRow.Select
For i = 1 To nb
Selection.Copy
Selection.Insert
Application.CutCopyMode = False
On Error Resume Next
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0 'exit
Next i
PrevLastRow = ActiveSheet.UsedRange.Rows.Count
insertOnGoing = False
Else:
' ajouter des colonnes
If Selection.Rows.Count = Rows.Count Then
'MsgBox "insert column"
nb = Selection.Columns.Count
ActiveCell.EntireColumn.Select
For i = 1 To nb
Selection.Copy
Selection.Insert
Application.CutCopyMode = False
On Error Resume Next
ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Next i
PrevLastColumn = ActiveSheet.UsedRange.Columns.Count
insertOnGoing = False
Else: MsgBox "Sélectionner soit une/des ligne(s) entière(s), soit une/des colonne(s) entière(s)"
End If
End If
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
' détecter l'insertion de ligne ou de colonne
If (insertOnGoing = False) And ((ActiveSheet.UsedRange.Rows.Count > PrevLastRow) And (0 = Selection.Cells.Count Mod Cells.Columns.Count) _
Or (ActiveSheet.UsedRange.Columns.Count > PrevLastColumn) And (0 = Selection.Cells.Count Mod Cells.Rows.Count)) Then
With Application
.EnableEvents = False
On Error Resume Next
.Undo ' annule l'insertion faite
On Error GoTo 0
.EnableEvents = True
End With
insertOnGoing = True
AddRowsOrColums
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' mémoriser les dernières lignes/colonnes en utilisation
PrevLastRow = ActiveSheet.UsedRange.Rows.Count
PrevLastColumn = ActiveSheet.UsedRange.Columns.Count
End Sub |
Partager