How to: Put Double Quotes in a String in Excel VBA

In VBA, strings assigned to variables or properties are enclosed in double quotes “”, Sometimes you might want to place Double Quotes “” in a string in Excel VBA. For example: A1 =IF(B1=”Excel”,B1,””) In VBE, you can replace ” with “”, and then enclose the entire string =IF(B1=””Excel””,B1,””””) in double quotes…

How To List All Files In Folder And Sub-folders Use Excel VBA

The following Excel VBA code can help you to list all files in folder and sub-folders into a worksheet. VBA Code How to Use This Macro To use this macro, you can copy and paste it into a standard module: Activate the Visual Basic Editor by pressing ALT+F11. Right-click the project/workbook name in…

Sort Column Data By Double Clicking Header Cell Use Excel VBA

If you want to sort the column data in ascending or descending order by double clicking on any Excel column header, you will need the following VBA code. See the screenshot: VBA code You can download the working example: Sort Column Data By Double Clicking Header. How to Use This…

Limit Scroll Area Use Excel VBA

The following VBA code can help you to limit the scroll area for a particular worksheet. VBA code Excel’s ScrollArea property allows you to set the scroll area for a particular worksheet. For instance, this statement sets the scroll area on Sheet1 so the user cannot activate any cells outside…

Find and Select the First Blank Cell in a Column VBA

Sometimes, You may need to find and select the first blank cell or last blank cell in a column, these macros can help you. Find and Select the First Blank Cell in Column A Sub Macro1() Dim ws As Worksheet Set ws = ActiveSheet For Each cell In ws.Columns(1).Cells If…

Find and Format All Formulas in a Workbook

This simple macro can help find and format all cells that contain formulas. VBA Code Sub FormatFormulas() ‘Step 1: Declare your Variables Dim ws As Worksheet ‘Step 2: Avoid Error if no formulas are found On Error Resume Next ‘Step 3: Start looping through worksheets For Each ws In ActiveWorkbook.Worksheets…

Delete Blank or Empty Columns Use Excel VBA

Blank columns can often cause problems with formulas. If you find that you are manually searching out and deleting blank columns in your data sets, this simple macro can help automate delete blank columns. If you want to delete blank rows, click here. VBA Code How This Macro Works In…

Delete Blank or Empty Rows Use Excel VBA

Blank rows can often cause problems with formulas. If you find that you are manually searching out and deleting blank rows in your data sets, this simple macro can help automate delete empty rows. If you want to delete blank columns, click here. VBA Code Sub DeleteBlankRows() ‘Step1: Declare your…