35 Essential VBA code for everyday Excel tasks

Introduction to 35 Essential VBA code for everyday Excel tasks

Microsoft Excel is a widely used software application for managing, analyzing, and visualizing data. Visual Basic for Applications (VBA) is a programming language integrated into Excel that allows users to customize and automate their workbooks. Despite the benefits of using VBA to automate tasks, many Excel users avoid it due to the difficulty of memorizing VBA codes or a lack of understanding of VBA programming.

In this blog post, we will demonstrate 35 Essential VBA code for everyday Excel tasks such as data processing and handling Excel files. The code examples cover a range of topics such as creating new files, inserting and moving sheets, protecting and deleting sheets, adding and formatting column headers, applying filters, identifying the last non-blank row, copying and cutting data, adding and deleting rows and columns, converting ranges to tables, and sending Excel files through email. We aim to provide a code repository that users can refer to and use for basic Excel tasks.

Let’s start with the 35 Essential VBA code for everyday Excel tasks

The following are VBA codes that address common scenarios encountered on a daily basis. You can easily copy and paste these codes into your project, and simply modify the references and other parts as needed.

In addition to the codes, explanations are also included for 35 Essential VBA code for everyday Excel tasks. If you need to understand how the codes work, simply refer to the explanations and make the necessary modifications. It’s a straightforward process! Right?

We would appreciate your feedback in the comment section for our article ’35 Essential VBA code for everyday Excel tasks’.

VBA code to create a new Excel file and save it:

Sub CreateNewFileAndSave()
    Dim FilePath As String
    Dim FileName As String
    
    ' Set the file path and name
    FilePath = "C:\Users\John\Documents\"
    FileName = "NewFile.xlsx"
    
    ' Create a new workbook
    Workbooks.Add
    
    ' Save the workbook with the specified name and path
    ActiveWorkbook.SaveAs FileName:=FilePath & FileName, FileFormat:=xlOpenXMLWorkbook
End Sub

Explanation:

In this code, the FilePath variable is set to the desired location where the file will be saved. The FileName variable is set to the desired name of the new Excel file.

The Workbooks.Add command creates a new workbook.

Finally, the ActiveWorkbook.SaveAs command saves the newly created workbook with the specified FileName and FilePath. The xlOpenXMLWorkbook file format is used, which is the default file format for Excel files with the .xlsx extension.

VBA code to create a new Excel file and save it with a password protection:

Sub CreateNewFileAndSaveWithPassword()
    Dim FilePath As String
    Dim FileName As String
    Dim Password As String
    
    ' Set the file path, name, and password
    FilePath = "C:\Users\John\Documents\"
    FileName = "NewFile.xlsx"
    Password = "myPassword"
    
    ' Create a new workbook
    Workbooks.Add
    
    ' Save the workbook with password protection
    ActiveWorkbook.SaveAs FileName:=FilePath & FileName, Password:=Password, FileFormat:=xlOpenXMLWorkbook
End Sub

Explanation:

In this code, the FilePath variable is set to the desired location where the file will be saved. The FileName variable is set to the desired name of the new Excel file. The Password variable is set to the desired password to protect the file.

The Workbooks.Add command creates a new workbook.

Finally, the ActiveWorkbook.SaveAs command saves the newly created workbook with the specified FileName, FilePath, and Password. The xlOpenXMLWorkbook file format is used, which is the default file format for Excel files with the .xlsx extension. Whenever the user tries to open this Excel file, they will be prompted to enter the password.

VBA code to insert a new worksheet in an existing Excel file:

Sub InsertWorksheet()
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook ' Set the workbook to the current workbook
    
    ' Add a new worksheet after the last worksheet in the workbook
    Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    
    ' Rename the new worksheet
    ws.Name = "New Worksheet"
End Sub

Explanation:

In this code, the Set wb = ThisWorkbook command sets the wb variable to the current workbook.

The Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)) command adds a new worksheet after the last worksheet in the workbook. The After parameter specifies the worksheet after which the new worksheet will be added. In this case, it is set to the last worksheet in the workbook.

Finally, the ws.Name = "New Worksheet" command renames the new worksheet to “New Worksheet”. You can change the name of the new worksheet as per your requirement.

VBA code to rename an Excel worksheet:

Sub RenameWorksheet()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Set the worksheet to be renamed
    
    ' Rename the worksheet
    ws.Name = "New Name"
End Sub

Explanation:

In this code, the Set ws = ThisWorkbook.Sheets("Sheet1") command sets the ws variable to the worksheet that you want to rename. You can change the "Sheet1" value to the actual name of the worksheet that you want to rename.

Finally, the ws.Name = "New Name" command renames the worksheet to “New Name”. You can change the name of the worksheet as per your requirement.

VBA code to make a duplicate copy of a worksheet:

Sub DuplicateSheet()
    Dim newSheet As Worksheet
    Dim originalSheet As Worksheet
    
    ' Set the original worksheet to be duplicated
    Set originalSheet = ThisWorkbook.Sheets("OriginalSheetName")
    
    ' Duplicate the original worksheet
    originalSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set newSheet = ActiveSheet
    
    ' Rename the new worksheet
    newSheet.Name = "NewSheetName"
End Sub

Explanation:

In this code, the DuplicateSheet subroutine starts by declaring two worksheet variables, newSheet and originalSheet. The originalSheet variable is set to the worksheet that will be duplicated.

Next, the Copy method is called on the originalSheet object to create a copy of the worksheet. The After parameter specifies where the copy should be inserted, in this case after the last worksheet in the workbook.

The ActiveSheet property is then used to set the newSheet variable to the newly created worksheet. Finally, the Name property is used to rename the new worksheet to the desired name.

VBA code to delete a worksheet:

Sub DeleteSheet()
    Dim sheetToDelete As Worksheet
    
    ' Set the worksheet to be deleted
    Set sheetToDelete = ThisWorkbook.Sheets("SheetName")
    
    ' Delete the worksheet
    Application.DisplayAlerts = False
    sheetToDelete.Delete
    Application.DisplayAlerts = True
End Sub

Explanation:

In this code, the DeleteSheet subroutine starts by declaring a worksheet variable, sheetToDelete. The sheetToDelete variable is set to the worksheet that will be deleted.

The DisplayAlerts property of the Application object is set to False to suppress the warning message that Excel displays when deleting a worksheet. This is done to prevent interruption during the program execution.

Next, the Delete method is called on the sheetToDelete object to delete the worksheet.

Finally, the DisplayAlerts property of the Application object is set back to True to re-enable warning messages in Excel.

VBA code to move an Excel worksheet:

Sub MoveWorksheet()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Set the worksheet to be moved
    
    ' Move the worksheet to the beginning of the workbook
    ws.Move Before:=ThisWorkbook.Sheets(1)
End Sub

Explanation:

In this code, the Set ws = ThisWorkbook.Sheets("Sheet1") command sets the ws variable to the worksheet that you want to move. You can change the "Sheet1" value to the actual name of the worksheet that you want to move.

Finally, the ws.Move Before:=ThisWorkbook.Sheets(1) command moves the worksheet to the beginning of the workbook, before the first worksheet. You can change the 1 value to the index of the worksheet before which you want to move the worksheet. For example, if you want to move the worksheet before the second worksheet, you can change the value to 2.

VBA code to copy an Excel worksheet to a new workbook:

Sub CopyWorksheetToNewWorkbook()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim ws As Worksheet
    
    ' Set the source workbook and worksheet to be copied
    Set wbSource = ThisWorkbook
    Set ws = wbSource.Sheets("Sheet1")
    
    ' Create a new workbook for the copied worksheet
    Set wbTarget = Workbooks.Add
    
    ' Copy the worksheet to the new workbook
    ws.Copy Before:=wbTarget.Sheets(1)
    
    ' Save the new workbook
    wbTarget.SaveAs "C:\NewWorkbook.xlsx"
End Sub

Explanation:

In this code, the Set wbSource = ThisWorkbook command sets the wbSource variable to the current workbook. You can change this to reference a different workbook if needed.

The Set ws = wbSource.Sheets("Sheet1") command sets the ws variable to the worksheet that you want to copy. You can change the "Sheet1" value to the actual name of the worksheet that you want to copy.

The Set wbTarget = Workbooks.Add command creates a new workbook that will contain the copied worksheet.

The ws.Copy Before:=wbTarget.Sheets(1) command copies the worksheet to the new workbook, before the first worksheet. You can change the 1 value to the index of the worksheet before which you want to copy the worksheet.

Finally, the wbTarget.SaveAs "C:\NewWorkbook.xlsx" command saves the new workbook to a file with the specified name and location. You can change the filename and location as per your requirement.

VBA code to move an existing worksheet to a new workbook and save it with a new name:

Sub MoveWorksheetToNewWorkbook()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim ws As Worksheet
    Dim newName As String
    
    ' Set the source workbook and worksheet to be moved
    Set wbSource = ThisWorkbook
    Set ws = wbSource.Sheets("Sheet1")
    
    ' Create a new workbook for the moved worksheet
    Set wbTarget = Workbooks.Add
    
    ' Move the worksheet to the new workbook
    ws.Move Before:=wbTarget.Sheets(1)
    
    ' Get the new name for the workbook
    newName = InputBox("Enter a name for the new workbook")
    
    ' Save the new workbook with the new name
    wbTarget.SaveAs "C:\" & newName & ".xlsx"
End Sub

Explanation:

In this code, the Set wbSource = ThisWorkbook command sets the wbSource variable to the current workbook. You can change this to reference a different workbook if needed.

The Set ws = wbSource.Sheets("Sheet1") command sets the ws variable to the worksheet that you want to move. You can change the "Sheet1" value to the actual name of the worksheet that you want to move.

The Set wbTarget = Workbooks.Add command creates a new workbook that will contain the moved worksheet.

The ws.Move Before:=wbTarget.Sheets(1) command moves the worksheet to the new workbook, before the first worksheet. You can change the 1 value to the index of the worksheet before which you want to move the worksheet.

The newName = InputBox("Enter a name for the new workbook") command prompts the user to enter a new name for the workbook.

Finally, the wbTarget.SaveAs "C:\" & newName & ".xlsx" command saves the new workbook to a file with the specified name and location. You can change the directory and file extension as per your requirement.

VBA code to protect a worksheet without a password:

Sub ProtectSheetWithoutPassword()
    ' Protect the worksheet without a password
    ThisWorkbook.Sheets("SheetName").Protect UserInterfaceOnly:=True
End Sub

Explanation:

In this code, the ProtectSheetWithoutPassword subroutine starts by calling the Protect method on the worksheet object. The UserInterfaceOnly parameter is set to True, which means that protection will only be applied when the worksheet is opened in Excel. This allows VBA code to modify the protected worksheet without requiring a password.

Note that when a worksheet is protected without a password, any user can unprotect it by going to the Review tab on the Excel ribbon and clicking the “Unprotect Sheet” button. If you need stronger protection for your worksheet, consider using a password to protect the sheet.

VBA code to protect a worksheet with a password:

Sub ProtectSheetWithPassword()
    ' Protect the worksheet with a password
    ThisWorkbook.Sheets("SheetName").Protect Password:="myPassword", UserInterfaceOnly:=True
End Sub

Explanation:

In this code, the ProtectSheetWithPassword subroutine starts by calling the Protect method on the worksheet object. The Password parameter is set to "myPassword", which is the password that will be required to unprotect the sheet. The UserInterfaceOnly parameter is set to True, which means that protection will only be applied when the worksheet is opened in Excel. This allows VBA code to modify the protected worksheet without requiring the password.

Note that when a worksheet is protected with a password, only users who know the password can unprotect it. If you forget the password, you will not be able to unprotect the worksheet. Be sure to keep the password in a secure location where you can retrieve it if needed.

VBA code to unprotect an Excel worksheet with a password:

Sub UnprotectWorksheetWithPassword()
    ' Unprotect the worksheet with a password
    ActiveSheet.Unprotect Password:="myPassword"
End Sub

Explanation:

In this code, the UnprotectWorksheetWithPassword subroutine is used to unprotect the active worksheet with the specified password. The Unprotect method is called on the ActiveSheet object with the Password parameter set to "myPassword", which is the password that was used to protect the worksheet.

When you run this code, the worksheet will be unprotected if the correct password is provided. If an incorrect password is provided, an error will occur. Note that if you forget the password, you will not be able to unprotect the worksheet. Be sure to keep the password in a secure location where you can retrieve it if needed.

VBA code to protect a specific range, such as A2:B10, with a password:

Sub ProtectRangeWithPassword()
    ' Protect the range A2:B10 with a password
    ThisWorkbook.Sheets("SheetName").Range("A2:B10").Locked = True
    ThisWorkbook.Sheets("SheetName").Protect Password:="myPassword", UserInterfaceOnly:=True
End Sub

Explanation:

In this code, the ProtectRangeWithPassword subroutine starts by locking the range A2:B10 by setting the Locked property to True. This will prevent any changes from being made to the range after it is protected. The Protect method is then called with the Password parameter set to "myPassword", which is the password that will be required to unprotect the sheet. The UserInterfaceOnly parameter is set to True, which means that protection will only be applied when the worksheet is opened in Excel. This allows VBA code to modify the protected range without requiring the password.

Note that when a range is protected with a password, only users who know the password can unprotect it. If you forget the password, you will not be able to unprotect the range. Be sure to keep the password in a secure location where you can retrieve it if needed.

VBA code to protect an Excel workbook with a password:

Sub ProtectWorkbookWithPassword()
    ' Protect the workbook with a password
    ThisWorkbook.Protect Password:="myPassword", Structure:=True, Windows:=False
End Sub

Explanation:

In this code, the ProtectWorkbookWithPassword subroutine is used to protect the workbook with a password. The Protect method is called with the Password parameter set to "myPassword", which is the password that will be required to unprotect the workbook. The Structure parameter is set to True, which prevents users from adding, moving, or deleting sheets in the workbook. The Windows parameter is set to False, which prevents users from resizing or moving the workbook window.

When you run this code, the workbook will be protected with the specified password. Note that when a workbook is protected with a password, only users who know the password can unprotect it. If you forget the password, you will not be able to unprotect the workbook. Be sure to keep the password in a secure location where you can retrieve it if needed.

VBA code to unprotect an Excel workbook with a password:

Sub UnprotectWorkbookWithPassword()
    ' Unprotect the workbook with a password
    ThisWorkbook.Unprotect Password:="myPassword"
End Sub

Explanation:

In this code, the UnprotectWorkbookWithPassword subroutine is used to unprotect the workbook with the specified password. The Unprotect method is called with the Password parameter set to "myPassword", which is the password that was used to protect the workbook.

When you run this code, the workbook will be unprotected if the correct password is provided. If an incorrect password is provided, an error will occur. Note that if you forget the password, you will not be able to unprotect the workbook. Be sure to keep the password in a secure location where you can retrieve it if needed.

VBA code to create column headers with specified column names and apply formatting to make it look like headers:

Sub CreateColumnHeaders()
    ' Define the header row
    Dim headerRow As Range
    Set headerRow = Range("A1:F1")
    
    ' Assign column names
    headerRow.Cells(1, 1).Value = "Date"
    headerRow.Cells(1, 2).Value = "Employee Name"
    headerRow.Cells(1, 3).Value = "Age"
    headerRow.Cells(1, 4).Value = "DOJ"
    headerRow.Cells(1, 5).Value = "Salary"
    headerRow.Cells(1, 6).Value = "Remarks"
    
    ' Apply formatting to the header row
    headerRow.Font.Bold = True
    headerRow.Interior.ColorIndex = 15
    headerRow.HorizontalAlignment = xlCenter
End Sub

Explanation:

In this code, the CreateColumnHeaders subroutine is used to create column headers with the specified column names. The headerRow variable is defined as a range object that represents the first row of the worksheet. The column names are assigned to each cell in the header row using the Value property.

After assigning the column names, the code applies formatting to the header row. The Font.Bold property is set to True to make the text bold. The Interior.ColorIndex property is set to 15 to apply a gray background color. The HorizontalAlignment property is set to xlCenter to center-align the text within each cell.

When you run this code, the header row will be created and formatted as specified.

VBA code to remove gridlines from the sheet:

Sub RemoveGridlines()
    ActiveSheet.DisplayGridlines = False
End Sub

Explanation:

This code simply sets the DisplayGridlines property of the active sheet to False, which removes the gridlines. You can assign this macro to a button or a keyboard shortcut for quick access.

VBA code to apply a border to a selected range:

Sub ApplyBorder()
    Selection.Borders.LineStyle = xlContinuous
    Selection.Borders.Weight = xlThin
    Selection.Borders.ColorIndex = xlAutomatic
End Sub

Explanation:

This code applies a continuous thin border to the selected range. You can modify the LineStyle, Weight, and ColorIndex properties of the Borders object to change the appearance of the border. You can also assign this macro to a button or a keyboard shortcut for quick access.

VBA code to copy the range A2:C40 from Sheet1 to Sheet2:

Sub CopyRange()
    Sheets("Sheet1").Range("A2:C40").Copy Destination:=Sheets("Sheet2").Range("A2")
End Sub

Explanation:

This code uses the Range object to select the range A2:C40 in Sheet1. Then it uses the Copy method to copy the range to the clipboard. Finally, it pastes the copied range to cell A2 in Sheet2 using the Destination argument of the Range object. You can modify the range addresses and sheet names according to your needs. Also, you can assign this macro to a button or a keyboard shortcut for quick access.

VBA code to cut the range A2:C40 from Sheet1 and paste it into Sheet2:

Sub CutAndPaste()
    Sheets("Sheet1").Range("A2:C40").Cut Destination:=Sheets("Sheet2").Range("A2")
End Sub

Explanation:

This code first selects the range A2:C40 in Sheet1 using Sheets("Sheet1").Range("A2:C40"). Then, it uses the Cut method to cut the selected range. The Destination parameter is used to specify where the cut range should be pasted, which in this case is A2 in Sheet2.

VBA code to apply bold, italic, and underline formatting on a selected range:

Sub applyFormatting()
    ' Select the range to format
    Range("A1:D10").Select
    
    ' Apply bold formatting
    Selection.Font.Bold = True
    
    ' Apply italic formatting
    Selection.Font.Italic = True
    
    ' Apply underline formatting
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

Explanation:

In this example, the Range method is used to select the range A1:D10, but you can modify this to select any range you want. The Selection object is then used to apply the formatting to the selected range. The Font property is used to access the font settings for the range, and the Bold, Italic, and Underline properties are used to apply the desired formatting. The xlUnderlineStyleSingle constant is used to specify a single underline style. You can modify this code to apply other formatting options as well, such as font color, font size, or cell background color, by accessing the appropriate properties of the Font object.

VBA code to check if a filter has been applied on a given range or not:

Sub CheckFilter()
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ActiveSheet ' Change to the worksheet name where you want to check the filter
    Set rng = ws.Range("A1:D10") ' Change to the range where you want to check the filter
    
    If rng.AutoFilterMode Then
        MsgBox "Filter has been applied on the selected range."
    Else
        MsgBox "Filter has not been applied on the selected range."
    End If
End Sub

Explanation:

In this example, we are checking if a filter has been applied on the range A1:D10 in the active worksheet. If the AutoFilterMode property of the range is True, it means a filter has been applied. Otherwise, it means no filter has been applied.

VBA code to remove filters from a range:

Sub RemoveFilters()
    'Specify the range from which you want to remove filters
    Dim rng As Range
    Set rng = Range("A1:F10")
    
    'Check if the range has filters applied or not
    If rng.Parent.AutoFilterMode = True Then
        'If filters are applied, then remove them
        rng.Parent.AutoFilterMode = False
    End If
End Sub

Explanation:

In the above code, we first specify the range from which we want to remove filters using the Range function. Then we check if filters are applied on the specified range using the AutoFilterMode property of the parent worksheet. If filters are applied, then we simply turn them off by setting the AutoFilterMode property to False.

VBA code to apply a filter on a single column in a given range:

Sub ApplyFilterOnSingleColumn()
    Dim rng As Range
    Set rng = Range("A1:F10") ' Change range as per your requirement
    
    If rng.AutoFilterMode = True Then
        rng.AutoFilter ' Remove existing filter if any
    End If
    
    rng.AutoFilter Field:=3, Criteria1:="CriteriaValue" ' Apply filter on 3rd column (change "CriteriaValue" with your desired value)
End Sub

Explanation:

In this code, we first set the range in which we want to apply the filter. Then we check if there is any existing filter applied to that range, and if so, we remove it. Finally, we apply a filter on the 3rd column of the range with the criteria value “CriteriaValue”. You can change the column number and criteria value as per your requirement.

VBA Code to filter a date column in Excel:

Sub FilterDateColumn()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change "Sheet1" to the name of your worksheet
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'change "A" to the column of your date column
    
    ws.Range("A1:A" & lastRow).AutoFilter Field:=1, Criteria1:=">=" & DateSerial(2023, 3, 1), Operator:=xlAnd, Criteria2:="<=" & DateSerial(2023, 3, 31) 'change "A" to the column of your date column and "2023, 3, 1" and "2023, 3, 31" to your desired start and end dates
End Sub

Explanation:

In this code, the ws variable is set to the worksheet where the date column is located. The lastRow variable is set to the last row of data in the date column.

The Range.AutoFilter method is used to filter the date column based on the specified criteria. In this example, the criteria are set to filter for dates between March 1st, 2023 and March 31st, 2023. You can adjust these criteria by changing the values passed to the DateSerial function. The Field parameter specifies which column to filter (in this case, column A).

Once the filter has been applied, only the rows that meet the specified criteria will be displayed in the worksheet.

VBA code to apply a filter on a number column in a given range:

Sub ApplyNumberFilter()
    
    Dim FilterRange As Range
    
    'Set the range to which the filter is to be applied
    Set FilterRange = Range("A1:D20")
    
    'Apply filter to column C (third column) in the range
    FilterRange.AutoFilter Field:=3, Criteria1:=">100"
    
End Sub

Explanation:

In this code, we have set the range A1:D20 to which the filter is to be applied. The AutoFilter method is used to apply the filter to the third column in the range (Field:=3). The criteria for the filter is set as numbers greater than 100 (Criteria1:=">100").

You can modify the range and criteria as per your requirement.

VBA Code to copy filtered data from sheet 1 and paste it to sheet 2:

Sub FilterAndCopy()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim filterRange As Range
    Dim lastRow As Long
    
    'Set worksheet objects
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    'Set range objects
    lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    Set rng1 = ws1.Range("A2:C" & lastRow)
    Set rng2 = ws2.Range("A2:C" & lastRow)
    Set filterRange = rng1.Columns(3)
    
    'Clear any previous filters
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    
    'Apply filter to column C
    filterRange.AutoFilter Field:=1, Criteria1:=">=10000", Operator:=xlAnd, Criteria2:="<=50000"
    
    'Copy visible data to Sheet2
    rng1.SpecialCells(xlCellTypeVisible).Copy rng2
    
    'Clear filter
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    
    MsgBox "Filtered data copied successfully!", vbInformation
    
End Sub

Explanation:

This code first defines the worksheet and range objects for Sheet1 and Sheet2, and then sets the range to be filtered to column C. It then clears any previous filters on Sheet1 and applies a new filter to column C, specifying a range of values to be filtered. The visible data in the filtered range is then copied to Sheet2. Finally, the filter is cleared and a message box appears to confirm that the data has been copied successfully.

Note that this code filters data based on a range of values in column C, but you can modify the code to filter data based on different criteria or columns.

VBA code to identify the last non-blank row in a sheet:

Sub LastNonBlankRow()

    Dim lastRow As Long
    lastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    MsgBox "The last non-blank row is " & lastRow

End Sub

Explanation:

This code uses the Find method to search for the last non-blank cell in the sheet. The What parameter is set to “*”, which means it will search for any non-blank cell. The SearchDirection parameter is set to xlPrevious, which means it will search from the bottom of the sheet upwards. The SearchOrder parameter is set to xlByRows, which means it will search row by row.

Once the last non-blank cell is found, the row number is stored in the lastRow variable. The MsgBox function is then used to display a message box with the row number. You can modify this code to do whatever you need to do with the last non-blank row number.

VBA code to identify the first and last non-blank row in filtered data:

Sub IdentifyFirstLastRowInFilteredData()

    Dim ws As Worksheet
    Dim rng As Range
    Dim firstRow As Long, lastRow As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
    Set rng = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
    
    firstRow = rng.Cells(1).Row
    lastRow = rng.Cells(rng.Cells.Count).Row
    
    MsgBox "First non-blank row: " & firstRow & vbCrLf & "Last non-blank row: " & lastRow
    
End Sub

Explanation:

This code first sets the worksheet object to the sheet containing the filtered data. Then it sets the range object to the first column of the filtered data. The SpecialCells method with xlCellTypeVisible parameter is used to exclude any rows hidden by the filter.

Next, it identifies the first and last non-blank rows in the filtered data by getting the row number of the first and last cell in the range. Finally, it displays a message box with the first and last non-blank row numbers. You can modify the code to use your own sheet and column range as needed.

VBA code to delete filtered rows only from a range:

Sub Delete_Filtered_Rows()
    Dim rng As Range
    Set rng = ActiveSheet.Range("A1:F100") 'Change range to your desired range
    
    'Check if any filter is applied
    If Not ActiveSheet.AutoFilterMode Then
        MsgBox "No filter is applied on the sheet."
        Exit Sub
    End If
    
    'Check if any rows are filtered
    If ActiveSheet.FilterMode And rng.SpecialCells(xlCellTypeVisible).Rows.Count <= 1 Then
        MsgBox "No rows are visible after applying filter."
        Exit Sub
    End If
    
    'Delete filtered rows
    On Error Resume Next
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    
    'Remove filter
    ActiveSheet.AutoFilterMode = False
    
    MsgBox "Filtered rows have been deleted successfully."
    
End Sub

Explanation:

This code will first check if any filter is applied on the sheet. If not, it will display a message and exit the sub. If any filter is applied, it will check if any rows are visible after applying the filter. If no rows are visible, it will display a message and exit the sub. If there are visible rows, it will delete only the filtered rows and remove the filter. Finally, it will display a message indicating that the filtered rows have been deleted successfully.

VBA code to cut the filtered rows from the active sheet and paste them into the next sheet:

Sub CutFilteredRows()

    Dim lastRow As Long
    Dim filterRange As Range
    Dim filteredRows As Range
    Dim nextSheet As Worksheet
    
    'Get the last row of the active sheet
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Get the filter range
    Set filterRange = Range("A1:A" & lastRow)
    
    'Apply the filter
    filterRange.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd
    
    'Get the filtered rows
    Set filteredRows = filterRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    
    'Create the next sheet if it doesn't exist
    On Error Resume Next
    Set nextSheet = ActiveSheet.Next
    If nextSheet Is Nothing Then
        Set nextSheet = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
        nextSheet.Name = "Filtered Rows"
    End If
    On Error GoTo 0
    
    'Cut and paste the filtered rows to the next sheet
    filteredRows.Cut Destination:=nextSheet.Range("A1")
    
    'Remove the filter
    filterRange.AutoFilter
    
End Sub

Explanation:

This code first identifies the last row of the active sheet and the range to be filtered. It then applies the filter to the range and gets the visible rows. It then creates the next sheet if it doesn’t exist and cuts and pastes the filtered rows into it. Finally, it removes the filter.

VBA code to send the current Excel file as an attachment in an email:

Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim FilePath As String
    Dim FileName As String

    ' Get the path and file name of the current Excel file
    FilePath = ThisWorkbook.Path & "\"
    FileName = ThisWorkbook.Name

    ' Create a new email and set the properties
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "recipient@example.com"
        .Subject = "Subject of the email"
        .Body = "Body of the email"
        .Attachments.Add FilePath & FileName
        .Send
    End With

    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Explanation:

In this code, we first get the path and file name of the current Excel file using ThisWorkbook.Path and ThisWorkbook.Name. Then, we create a new email using the CreateObject method and set its properties such as the recipient, subject, body, and attachment using the To, Subject, Body, and Attachments.Add methods, respectively. Finally, we send the email using the Send method and clean up the objects using the Set statement.

VBA code to send the active sheet as an attachment in an email:

Sub SendActiveSheetAsAttachment()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim FileName As String
    
    'Get the file name of the active workbook
    FileName = ActiveWorkbook.FullName
    
    'Create a new email message
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    'Attach the active sheet to the email
    With OutMail
        .To = "recipient@email.com"
        .Subject = "Active Sheet"
        .Attachments.Add ActiveSheet.Parent.Path & "\" & ActiveSheet.Name
        .Send
    End With
    
    'Release memory
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Explanation:

This code uses the Outlook application to create a new email message and attach the active sheet as an attachment. The email is then sent immediately. You will need to replace “recipient@email.com” with the email address of the recipient.

VBA code to copy a range A1:F20 from Sheet1, paste it in the body of an email, and sends it using Microsoft Outlook:

Sub SendEmailWithRange()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim myRange As Range
    Dim Signature As String
    
    'Set email range to copy
    Set myRange = ThisWorkbook.Sheets("Sheet1").Range("A1:F20")
    
    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    'Copy range to clipboard
    myRange.Copy
    
    'Create email body with range
    With OutlookMail
        .Display
        Signature = .HTMLBody
        .HTMLBody = "<html><body><font face=""Calibri"">" & _
                    "<p>Hi,</p><p>Please find attached the requested data:</p>" & _
                    RangetoHTML(myRange) & _
                    "<p>Regards,</p><p>Your Name</p></font></body></html>" & Signature
        .Subject = "Data Report"
        .To = "recipient@example.com"
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With
    
    'Clear clipboard
    Application.CutCopyMode = False
    
    'Release Outlook objects from memory
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    
End Sub

'Function to convert range to HTML for email body
Function RangetoHTML(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    
    'Create temporary HTML file
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
    'Create file system object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.CreateTextFile(TempFile, True)
    
    'Write HTML tags and range to file
    With ts
        .WriteLine "<table border=1>"
        .WriteLine "<tr><th colspan=" & rng.Columns.Count & "><font face=""Calibri"" size=""3"">" & rng.Parent.Name & "</font></th></tr>"
        .WriteLine "<tr>"
        For Each c In rng.Cells
            .WriteLine "<td><font face=""Calibri"" size=""3"">" & c.Value & "</font></td>"
        Next
        .WriteLine "</tr>"
        .WriteLine "</table>"
    End With
    
    'Read file contents to variable and delete temporary file
    RangetoHTML = fso.OpenTextFile(TempFile, 1).ReadAll
    ts.Close
    fso.DeleteFile TempFile
    
    'Release file system object from memory
    Set ts = Nothing
    Set fso = Nothing
End Function

Explanation:

Note that this code uses a function called RangetoHTML to convert the copied range to HTML format for the email body. This function is defined at the bottom of the code and should be included in the same module.

VBA code to save and close an Excel file:

Sub SaveAndCloseFile()

    ' Save the active workbook
    ActiveWorkbook.Save

    ' Close the active workbook
    ActiveWorkbook.Close

End Sub

Explanation:

This code uses the Save method to save the active workbook, and the Close method to close it. If there are any unsaved changes, Excel will prompt you to save them before closing the file.

You can call this subroutine from another macro or from the Immediate window by typing its name and pressing Enter.

Feedback or questions on 35 Essential VBA code for everyday Excel tasks

If you feel that there are some commonly used VBA codes that we have missed out on in our article ’35 Essential VBA code for everyday Excel tasks’, please do let us know in the comment section. Our team will be happy to add those codes to the article. Thank you.

Visit our YouTube channel @TheDataLabs for step-by-step tutorial.