Create Flash Fill with Excel VBA

Creating a Flash Fill using VBA in Excel can be a powerful way to automate the process of splitting, combining, or formatting data based on patterns recognized in the data. Flash Fill is typically a feature in Excel that automatically fills in values based on the pattern you create in the adjacent cells. Although Flash Fill is a built-in feature in Excel, you can replicate a similar effect using VBA.

Here’s a detailed explanation and code on how to create Flash Fill behavior with VBA in Excel.

Problem Overview:

You want to automatically fill a column of data based on a pattern in an adjacent column. For example, if you have a list of names like « John Smith » in column A, you might want to automatically split them into « John » and « Smith » in columns B and C.

Steps:

1. Identify the Pattern: The user will define the pattern in one or two example rows.
2. Apply VBA to Fill the Data: The VBA code will use that example to fill the remaining cells by detecting the pattern.

VBA Code to Simulate Flash Fill:

Sub CreateFlashFill()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim pattern As String
    Dim fillRange As Range
    Dim inputColumn As Range
    Dim outputColumn As Range
    Dim i As Long   
    ' Set the worksheet and input/output columns
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    Set inputColumn = ws.Range("A2:A20") ' Set the range for the input data
    Set outputColumn = ws.Range("B2:B20") ' Set the range for the output (Flash Fill simulation)   
    ' Clear previous data in the output column
    outputColumn.ClearContents   
    ' Get the pattern from the first row (example)
    pattern = inputColumn.Cells(1, 1).Value ' Change this as per your pattern   
    ' Loop through the input data range
    For Each cell In inputColumn
        ' Check if the cell value matches the pattern
        If cell.Value <> "" Then
            ' Based on the pattern, split or transform the value
            ' Example: Here we're splitting the full name into first and last name
            Dim parts() As String
            parts = Split(cell.Value, " ") ' Example: Split by space (adjust as needed)           
            ' Check if there are multiple parts to handle
            If UBound(parts) >= 0 Then
                ' In this case, we put the first part in the output column
                outputColumn.Cells(cell.Row - 1, 1).Value = parts(0) ' First name
            End If
            If UBound(parts) > 0 Then
                ' If there's a second part, put it in the next cell
                outputColumn.Cells(cell.Row - 1, 2).Value = parts(1) ' Last name
            End If
        End If
    Next cell
End Sub

Detailed Explanation:

1. Set Worksheet and Columns:
   • We first define the worksheet where the input data is located (ws = ThisWorkbook.Sheets(« Sheet1 »)).
   • We set the inputColumn to the range of data (e.g., A2:A20) and outputColumn to where we want to simulate Flash Fill behavior (e.g., B2:B20).
2. Pattern Definition:
   • The pattern is defined by the first example in the input column (pattern = inputColumn.Cells(1, 1).Value). This is where the user manually provides the first example of the transformation (like « John Smith » in the first row).
3. Looping through Input Data:
   • The VBA code then loops through the range of the input column (For Each cell In inputColumn).
   • If the cell is not empty, it proceeds to the transformation. In this case, we’re splitting the names by space (Split(cell.Value,  » « )), but you could adapt this to different patterns such as separating dates, addresses, etc.
4. Splitting and Filling Output:
   • After splitting the string into parts, the code places the first part (first name) into the corresponding cell in the output column (outputColumn.Cells(cell.Row – 1, 1).Value = parts(0)).
   • If a second part exists (last name), it places it in the adjacent column (outputColumn.Cells(cell.Row – 1, 2).Value = parts(1)).
5. Final Output:
   • After running the macro, column B and C will be automatically filled based on the pattern defined in the first row.

Customization:

• Pattern Matching: You can modify the Split() function or add custom logic for your specific pattern (e.g., if you need to separate dates, extract certain numbers, or format strings).
• Dynamic Ranges: If the range is dynamic (not always 20 rows), you can adjust the input and output ranges to automatically detect the last used row:

• Set inputColumn = ws.Range(« A2:A » & ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row)

Flash Fill Limitation:

Unlike Excel’s built-in Flash Fill feature, which uses machine learning to detect patterns based on multiple examples, this VBA solution requires defining the pattern manually and only fills based on the first instance.

Conclusion:

This VBA code helps to automate the pattern-based filling similar to Flash Fill in Excel. It can be expanded to work with more complex patterns and customized for different data manipulations based on user needs.