Introduction:

Excel VBA (Visual Basic for Applications) is a powerful tool that allows you to automate tasks and enhance your productivity in Excel. With VBA, you can write custom code to perform repetitive actions, manipulate data, and create customized solutions. In this article, we will share five Excel VBA code snippets that can help you streamline your day-to-day work and boost your productivity.

Tip 1: AutoFill Column with Formula:

When working with large datasets, you often need to apply the same formula to multiple cells in a column. Instead of manually copying and pasting the formula, you can use VBA to automate this process. Here’s a code snippet that demonstrates how to autofill a formula in a column:

Sub AutoFillFormula()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("B2:B" & lastRow).Formula = "=A2*2" ' Change the formula as needed
End Sub

This code finds the last row in column A, and then autofills the formula “=A2*2” in column B from row 2 to the last row. Adjust the formula and column references to suit your needs.

Tip2: Remove Duplicates:

Removing duplicates from a dataset is a common task to ensure data integrity and accuracy. Instead of using the built-in Excel feature, you can use VBA to automate this process. Here’s a code snippet to remove duplicates from a specific column:

Sub RemoveDuplicates()
    Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlYes ' Change column as needed
End Sub

This code removes duplicates from column A, considering the first row as a header. Modify the column reference as per your requirement.

Tip 3: Filter Data and Copy to New Sheet:

When you need to filter data based on specific criteria and copy the filtered results to a new sheet, VBA can simplify the process. Here’s a code snippet that demonstrates this:

Sub FilterAndCopy()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    'Change source sheet name

    Set wsSource = ThisWorkbook.Worksheets("Sheet1") 

    'Change target sheet name
    Set wsTarget = ThisWorkbook.Worksheets("Sheet2") 
    
    ' Change criteria and range as needed
    wsSource.Range("A1:D10").AutoFilter Field:=1, Criteria1:="Category1"
    ' Change range as needed
    wsSource.Range("A2:D10").SpecialCells(xlCellTypeVisible).Copy wsTarget.Range("A1") 
    
    wsSource.AutoFilterMode = False
End Sub

This code filters data in range A1:D10 on “Sheet1” based on the criteria “Category1” in the first column. The filtered results are then copied to “Sheet2” starting from cell A1. Adjust the sheet names, criteria, and range according to your data.

Tip 4: Export Worksheet as PDF:

If you frequently need to save an Excel worksheet as a PDF file, you can automate this process using VBA. Here’s a code snippet that exports the active worksheet as a PDF:

Sub ExportAsPDF()
    Dim filePath As String
    ' Change path and name
    filePath = "C:\Folder\FileName.pdf" 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard
End Sub

Replace the file path and name with the desired location and file name for the PDF export.

Tip 5: Generate Random Numbers:

Generating random numbers can be useful in various scenarios, such as creating sample data or conducting simulations. VBA provides a function called Rnd that generates random numbers. Here’s a code snippet to generate random numbers in a range:

Sub GenerateRandomNumbers()
    Dim rng As Range, cell As Range
    ' Change range as needed
    Set rng = Range("A1:A10") 
    
    For Each cell In rng
        ' Generate random number between 1 and 100
        cell.Value = Int((100 - 1 + 1) * Rnd + 1) 
    Next cell
End Sub

This code generates random numbers between 1 and 100 and populates them in the range A1:A10. Adjust the range and number range as per your requirements.

Conclusion:

Excel VBA provides immense power to automate tasks and improve productivity in day-to-day work. The five code snippets shared in this article cover various scenarios, including autofilling formulas, removing duplicates, filtering and copying data, exporting worksheets as PDFs, and generating random numbers. By incorporating these VBA code snippets into your workflow, you can save time and effort, allowing you to focus on more critical aspects of your work in Excel.

FREE Download

Download all the above 5 code snippets at once. It is in a text file. You can copy all the code at once and paste in your excel VBA module of your workbook and you are good to go.