James a des données dans une feuille de calcul qui est contenue dans une série de lignes. L’une des colonnes des données comprend des cellules contenant plusieurs lignes par cellule. (Les données de la cellule ont été séparées en lignes en appuyant sur Alt + Entrée entre les éléments.) James aimerait diviser ces données en plusieurs lignes. Par exemple, s’il y avait trois lignes de données dans une seule cellule de la ligne, les données de cette cellule doivent être divisées en trois lignes.

Excel fournit un moyen pratique de diviser les données en colonnes séparées à l’aide de l’outil Texte en colonnes. Cela peut être utilisé pour diviser les données en fonction de la présence du caractère ASCII 10, ce qu’Excel insère lorsque vous appuyez sur Alt + Entrée. Le problème est que même si cela divise avec succès les données en colonnes séparées, il ne les obtient pas en lignes séparées, comme James l’a demandé.

Cela signifie que la solution à ce problème doit inclure l’utilisation d’une macro. Une approche est illustrée dans le code suivant. Dans cet exemple, la macro suppose que vous souhaitez «développer» tout dans la feuille de calcul et que les données de la feuille de calcul commencent à la ligne 1.

Sub CellSplitter1()

Dim Temp As Variant     Dim CText As String     Dim J As Integer     Dim K As Integer     Dim L As Integer     Dim iColumn As Integer     Dim lNumCols As Long     Dim lNumRows As Long

iColumn = 4

Set wksSource = ActiveSheet     Set wksNew = Worksheets.Add

iTargetRow = 0     With wksSource         lNumCols = .Range("IV1").End(xlToLeft).Column         lNumRows = .Range("A65536").End(xlUp).Row         For J = 1 To lNumRows             CText = .Cells(J, iColumn).Value             Temp = Split(CText, Chr(10))

For K = 0 To UBound(Temp)

iTargetRow = iTargetRow + 1                 For L = 1 to lNumCols                     If L <> iColumn Then                         wksNew.Cells(iTargetRow, L) _                           = .Cells(J, L)

Else                         wksNew.Cells(iTargetRow, L) _                           = Temp(K)

End If                 Next L             Next K         Next J     End With End Sub

Notez que pour exécuter la macro, vous devrez spécifier, à l’aide de la variable iColumn, la colonne contenant les cellules à séparer.

Comme écrit ici, la macro sépare les informations dans la quatrième colonne. En outre, les versions séparées des cellules sont stockées dans une nouvelle feuille de calcul, de sorte que la feuille de calcul d’origine n’est pas du tout affectée.

La macro repose sur l’utilisation de la fonction Split pour déchirer les cellules multilignes. Cette fonction n’est disponible qu’à partir d’Excel 2000 et n’est pas du tout disponible dans Excel pour Mac. En outre, vous souhaiterez peut-être exécuter la macro uniquement sur une sélection particulière de cellules. Pour surmonter tous ces problèmes potentiels, vous voudrez plutôt considérer la macro suivante:

Sub CellSplitter2()

Dim iSplitCol As Integer     Dim iEnd As Integer     Dim sTemp As String     Dim iCount As Integer     Dim i As Integer     Dim wksNew As Worksheet     Dim wksSource As Worksheet     Dim lRow As Long     Dim lRowNew As Long     Dim lRows As Long     Dim lRowOffset As Long     Dim iTargetRows As Integer     Dim iCol As Integer     Dim iCols As Integer     Dim iColOffset As Integer     Dim AWF As WorksheetFunction

On Error GoTo ErrRoutine     Application.ScreenUpdating = False

'Set Column to split     iSplitCol = 4

iCols = Selection.Columns.Count     lRows = Selection.Rows.Count

iColOffset = Selection.Column - 1     lRowOffset = Selection.Row - 1     lRowNew = lRowOffset          Set wksSource = ActiveSheet     Set wksNew = Worksheets.Add

Set AWF = Application.WorksheetFunction     With wksSource         For lRow = (lRowOffset + 1) To (lRowOffset + lRows)

sTemp = .Cells(lRow, iSplitCol)

If Right(sTemp, 1) <> vbLf Then                 sTemp = sTemp & vbLf             End If             iCount = (Len(sTemp) - _               Len(AWF.Substitute(sTemp, vbLf, "")))



For iTargetRows = 1 To iCount                 lRowNew = lRowNew + 1                 For i = (iColOffset + 1) To (iColOffset + iCols)

If i <> iSplitCol Then                         wksNew.Cells(lRowNew, i) _                           = .Cells(lRow, i)

Else                         iEnd = InStr(sTemp, vbLf)

wksNew.Cells(lRowNew, i) _                           = Left(sTemp, iEnd - 1)

sTemp = Mid(sTemp, iEnd + 1)

End If                 Next i             Next iTargetRows         Next lRow     End With

ExitRoutine:

Set wksSource = Nothing     Set wksNew = Nothing     Set AWF = Nothing     Application.ScreenUpdating = True     Exit Sub

ErrRoutine:

MsgBox Err.Description, vbExclamation     Resume ExitRoutine End Sub

La macro repose toujours sur l’utilisation d’une variable pour indiquer la colonne à séparer. Dans cet exemple, la variable est iSplitCol et elle est définie sur la colonne 4. La macro fonctionne uniquement sur les cellules sélectionnées lors de sa première exécution et les cellules séparées sont transférées vers une nouvelle feuille de calcul. L’adresse de la cellule supérieure gauche dans la nouvelle feuille de calcul est la même que la cellule supérieure gauche sélectionnée lorsque la macro est exécutée.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3263) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Splitting_Information_into_Rows [Diviser les informations en lignes].