Excel VBA clear cell contents with ClearContents Method

Posted on October 15, 2015October 16, 2015 by Wyman W

This Excel tutorial explains how to clear cell contents / clear cell formats using Clear, ClearContents, ClearFormats Method. Excel VBA clear cell contents There are several kinds of "clear" related Excel VBA Methods for different scenarios. I will also explain how to clear single cell contents, clear merged cells contents, and clear blank cells. Clear single cell contents with ClearContents There are two ways to clear single cell contents (clear value in a Cell but not formatting) As...

Excel Delete Blank Rows

Posted on October 12, 2015February 26, 2017 by Wyman W

This Excel tutorial explains how to delete blank rows in Excel with VBA and without VBA. You may also want to read: Excel delete blank rows and blank cells before import into Access Excel delete duplicated data in consecutive rows Excel Delete Blank Rows Sometimes we have data that contains blank rows, one example is data with pivot table layout where data are grouped together and repeated items are blank. Previously I wrote a post about filling blank rows, this post is about delet...

Excel VBA separate line break data into different rows

Posted on August 31, 2015March 30, 2017 by Wyman W

This Excel VBA tutorial explains how to separate line break data into different rows. You may also want to read: Access Excel remove line break and carriage return Excel VBA separate line break data into different rows Recently I received a report from a HR system which is claimed to be a standard report. In the report, some data are grouped into one Cell and are separated by line break. I found this report format totally unacceptable. From the database perspective, each dependent...

Excel verify Text format and convert number to text

Posted on July 11, 2015 by Wyman W

This Excel tutorial explains how to verify Text format in a column. If it is a Number then convert Number to Text. You may also want to read: Excel verify Number format and convert Text to Number Excel verify Text format Three kinds of Cell contents are considered as Text 1) Non-number - such as alphabet, symbol 2) A mix of number and non-number 3) Number with small triangle on the top left of the Cell Number can be in two forms in Excel - Text and Number. If a Number is a ...

Excel VBA DIR Function

Posted on July 2, 2015July 2, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder. You may also want to read: Excel loop workbooks in folders and subfolders with FileSystemObject Excel VBA DIR Function DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes). DIR Function is usually used with loop in order to manipulate multiple files of specific name. Syntax of DIR Function Dir(pathname, [attributes]) ...

Excel export Excel to PDF with and without VBA

Posted on June 27, 2015December 14, 2015 by Wyman W

This Excel tutorial explains how to export Excel to PDF with and without VBA, how to export all worksheets to one PDF, and export all worksheets to separate PDF. You may also want to read: Select multiple worksheets using Excel VBA Excel export Excel to PDF In Excel, navigate to File > Save As, in the Save As Type, you can save a file as different file types. Below is the available types in Excel 2013. In this tutorial we are going to focus on exporting Excel to PDF. Now se...

Excel consolidate all Comments in the workbook

Posted on June 26, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro. You may also want to read: Excel show all comments in worksheet Excel delete all comments in Workbook Excel consolidate all Comments in the workbook Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook. VBA Code - consolidate all comments Public Sub consolComments() counter = 1 ...

Excel show all comments in worksheet

Posted on June 23, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to show all comments and hide all comments in workbook. You may also want to read: Excel consolidate all Comments in the workbook Excel delete all comments in Workbook Excel show comments in worksheet Comment is a yellow dialog box that you can write comment regarding specific Cell. To insert a Comment, right click on a Cell and then select Insert Comment When we insert a Comment into a Cell, there would be a red triangle on the top right of ...

Excel Macro remove space in prefix and suffix

Posted on June 10, 2015September 10, 2015 by Wyman W

This Excel tutorial explains how to use Excel Macro to remove space in prefix and suffix in all worksheets. Excel Macro remove space in prefix and suffix Most recently I performed data validation on Excel spreadsheet in which the data contained a lot of spaces in prefix and suffix. I guess this happened because they delimited the data string using space. For example, seperating first name from full name. Suffix with space is not possible to identify by eye, but I had to remove all spaces ...

Excel loop workbooks in folders and subfolders with FSO

Posted on May 5, 2015March 8, 2016 by Wyman W

This Excel tutorial explains how to loop workbooks in folders and subfolders with FileSystemObject (FSO). You may also want to read: Excel VBA Dir Function to loop through workbooks in folder Add password to all Excel workbook in folder Excel loop workbooks in folders and subfolders FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, since we are discussing how to loop workbook in folders and subfo...