How to Add Month-Year and Year Columns with a Simple VBA Macro!

Automate Your Excel Workflow: How to Add Month-Year and Year Columns with a Simple VBA Macro!
VBA code

Automate Your Excel Workflow: How to Add Month-Year and Year Columns with a Simple VBA Macro!

VBA Code in html format

Explanation of the VBA Code: AddMonthYearColumn

This VBA code automates the process of adding two new columns to a worksheet: one for “Month-Year” and another for “Year”, based on a date column. It also formats the columns, applies filters, and ensures the new data is readable. Here’s a breakdown of the code:

1. Defining Variables and Setting the Worksheet

vba

• ws: Refers to the worksheet where the macro will operate.
• lastRow: Holds the number of the last row in the date column.
• dateColumn: Specifies the column containing date values (G in this case).
• outputColumn and outputColumnYear: Specify the columns where “Month-Year” and “Year” data will be added (H and I).

2. Identifying the Last Row in the Date Column

vba

lastRow = ws.Cells(ws.Rows.Count, dateColumn).End(xlUp).Row

• Finds the last non-empty row in the dateColumn. This ensures the macro processes only the rows with data.

3. Adding Column Headers

vba

ws.Cells(3, outputColumn).Value = “Month-Year”

ws.Cells(3, outputColumn).Font.Bold = True

ws.Cells(3, outputColumnYear).Value = “Year”

ws.Cells(3, outputColumnYear).Font.Bold = True

• Adds headers (“Month-Year” and “Year”) to the specified columns (H and I) in row 3.
• Makes the headers bold for better visibility.

4. Looping Through the Rows to Extract Date Information

vba

For i = 4 To lastRow

If IsDate(ws.Cells(i, dateColumn).Value) Then

ws.Cells(i, outputColumn).Value = Format(ws.Cells(i, dateColumn).Value, “mmmm-yyyy”)

ws.Cells(i, outputColumnYear).Value = Format(ws.Cells(i, dateColumn).Value, “yyyy”)

Else

ws.Cells(i, outputColumn).Value = “Invalid Date”

ws.Cells(i, outputColumnYear).Value = “Invalid Date”

End If

Next i

• Loops from row 4 (assumes row 3 contains headers) to the last row.
• Checks if the value in the dateColumn is a valid date:
  • If valid: Extracts the “Month-Year” (e.g., January-2023) and “Year” (e.g., 2023).
  • If invalid: Fills the corresponding cells with “Invalid Date”.

5. Clearing Existing Filters

vba

If ws.AutoFilterMode Then

ws.AutoFilterMode = False

End If

• Removes any pre-existing filters on the worksheet to avoid conflicts when applying new filters.

6. Applying Filters

vba

Dim lastColumn As Long

lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(3, 1), ws.Cells(lastRow, lastColumn)).AutoFilter

• Identifies the last used column (lastColumn) and applies filters to all columns from row 3 to the last row and column.
• This allows users to filter and analyze the data interactively.

7. Autofitting Columns for Readability

vba

ws.Columns(outputColumn).AutoFit

ws.Columns(outputColumnYear).AutoFit

• Adjusts the column widths of the “Month-Year” and “Year” columns for better readability.

8. Completion Message

vba

MsgBox “Month-Year and Year columns added and filter applied successfully!”, vbInformation

• Displays a message box to inform the user that the operation was successful.

YouTube link: https://youtu.be/yYUW-FFaemU

Example Workflow

Scenario: You have a worksheet with the following setup:

• Column G contains dates.
• You want to add “Month-Year” and “Year” data to columns H and I, respectively.

What Happens:

1. The macro identifies the last row in column G (dates).
2. It loops through each row starting from row 4:
   • Converts the date into “Month-Year” (e.g., January-2023) in column H.
   • Extracts the year (e.g., 2023) in column I.
3. Filters are applied to all rows and columns for easy navigation.
4. The columns are autofitted for better readability.

Benefits of This Code

1. Automation: Saves time by automating the process of extracting and organizing date-related data.
2. Error Handling: Marks invalid dates with “Invalid Date”, making it easy to spot issues.
3. User-Friendly: Applies filters and adjusts column widths to enhance usability.

How to Use

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module (Insert → Module) and paste the code.
4. Run the macro (Run → Run Sub/UserForm or F5).
5. Observe the results on the first worksheet.

Let me know if you’d like further customization or clarification!

VBA credit: Tryfon Papadopoulos