Documentation for VBA Code: SupprimerLeDernierTableau

Overview

The subroutine SupprimerLeDernierTableau deletes the last populated table of data in an Excel worksheet, starting from cell B9. It prompts the user for confirmation before proceeding with the deletion.

Code Details

Subroutine: SupprimerLeDernierTableau

Purpose

To remove the last table (range of data) starting at cell B9 in the current worksheet after user confirmation.

Parameters

• This subroutine does not accept any parameters.

Local Variables

• nbLignesDernierTableau (Long): Stores the number of rows in the last data range starting at cell B9.
• i (Integer): Counter used for looping through the rows to be deleted.
• reponseUtilisateur (String): Stores the response from the user confirmation dialog.

Return Type

• This subroutine does not return any values.

Logic and Flow

1. User Confirmation:

   • A message box prompts the user to confirm the deletion.
   • If the response is Yes, the code proceeds; otherwise, it exits.

2. Determine Rows in Last Table:

   • The number of rows in the table starting from B9 is calculated using the Range and Cells objects.

3. Check Valid Row Range:

   • The subroutine checks if the number of rows is within a reasonable range (greater than 1 and less than 100).

4. Delete Rows:

   • If the condition is met, it iterates through each row and deletes it, consistently removing the entire row containing cell B9.

Example Usage

Call SupprimerLeDernierTableau

Code with Comments

Sub SupprimerLeDernierTableau()
    ' Variable to hold the number of rows in the last table
    Dim nbLignesDernierTableau As Long
    ' Counter for loop
    Dim i As Integer
    
    ' Variable to store user response from the message box
    Dim reponseUtilisateur As String
    
    ' Prompt user for confirmation
    reponseUtilisateur = MsgBox("Vous allez supprimer le dernier tableau en date. Voulez-vous continuer?", vbQuestion + vbYesNo, "Confirmation")

    ' Check if the user confirmed the deletion
    If reponseUtilisateur = vbYes Then
        ' Calculate the number of rows in the last table starting from cell B9
        nbLignesDernierTableau = Range(Cells(9, 2), Cells(9, 2).End(xlToRight).End(xlDown)).Rows.Count
        
        ' Check if the number of rows is within a reasonable range
        If nbLignesDernierTableau > 1 And nbLignesDernierTableau < 100 Then
            ' Loop to delete each row in the range
            For i = 1 To nbLignesDernierTableau + 1
                Range("B9").EntireRow.Delete
            Next i
        End If
    End If
End Sub

Conclusion

This subroutine provides a clear and concise method to remove the last table starting from cell B9 in an Excel worksheet upon user confirmation. To maintain code maintainability and clarity, comments and explanations have been added throughout the subroutine.

For further study and skill enhancement, consider exploring the Enterprise DNA Platform's resources on VBA and Excel automation techniques.