Automate Data reformatting processes in Excel using VBA

This code covers several common tasks like:

1. Removing empty rows
2. Converting text to uppercase
3. Trimming extra spaces
4. Converting dates to a specific format
5. Formatting numbers
6. Sorting the data

Detailed VBA Code

Sub ReformatData()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim lastCol As Long
    ' Set the active sheet (or specify a particular sheet)
    Set ws = ActiveSheet
    ' Find the last row and last column with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Step 1: Remove empty rows
    For i = lastRow To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
    ' Update the last row after deletion
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Step 2: Convert text to uppercase for the entire data range
    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    ' Step 3: Trim extra spaces
    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = Trim(cell.Value)
        End If
    Next cell
    ' Step 4: Convert dates to a specific format (DD/MM/YYYY)
    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        If IsDate(cell.Value) Then
            cell.Value = Format(cell.Value, "dd/mm/yyyy")
        End If
    Next cell
    ' Step 5: Format numbers (2 decimal places)
    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        If IsNumeric(cell.Value) Then
            cell.NumberFormat = "0.00" ' Format with 2 decimal places
        End If
    Next cell
    ' Step 6: Sort the data (e.g., ascending sort by column 1)
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range(ws.Cells(1, 1), ws.Cells(lastRow, 1)), _
        Order:=xlAscending
    ws.Sort.SetRange Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ws.Sort.Header = xlYes ' If you have headers
    ws.Sort.Apply
    ' End of process alert
    MsgBox "The data has been successfully reformatted!", vbInformation
End Sub

Detailed Explanation

1. Removing Empty Rows:
   • The code loops through each row starting from the bottom and checks if the row is empty using CountA. If a row is empty, it is deleted with the .Delete method.
2. Converting Text to Uppercase:
   • The loop checks every cell in the data range. If the cell contains text (a string), it converts the text to uppercase using the UCase function.
3. Trimming Extra Spaces:
   • This step uses the Trim function to remove any leading or trailing spaces from the text values in the cells.
4. Converting Dates to a Specific Format:
   • If a cell contains a date, it is reformatted to DD/MM/YYYY using the Format function.
5. Formatting Numbers:
   • For cells containing numeric values, the NumberFormat property is set to « 0.00 » to ensure the numbers are displayed with 2 decimal places.
6. Sorting the Data:
   • The data is sorted based on the values in the first column (Column 1 in this case) in ascending order. You can adjust this to sort by any other column or in a descending order as needed.

How to Use the Code

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, insert a new module (click Insert > Module).
3. Copy and paste the code above into the module.
4. Run the code by pressing F5 or assign it to a button on your Excel worksheet.

Customization

You can customize this code for your specific needs:

• Adjust the range of cells on which to apply the transformations.
• Change the date or number format as per your preference.
• Modify the sorting criteria (for example, sort by a different column or in descending order).

This script serves as a great starting point for automating common data cleaning and reformatting tasks in Excel via VBA.