Excel VBA find all Cells contain Named Range

This Excel VBA tutorial explains how to find all Cells contain Named Range using Macro. You may also want to read: Find all external links and broken links in workbook Replace Named Range with formula Find all Cells contain Named Range In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) We can refer to Name Manager to see the formula the Named Range is referring to, but it does not tell which Cells are using the Named Range. Therefore...
More

Excel VBA clear cell contents with ClearContents Method

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...
More

Excel Delete Blank Rows

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 VBA reformat merged cells to row Fill Blank Rows or blank Cells 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, ...
More

Access Excel remove line break and carriage return

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text. You may also want to read: Excel VBA separate line break data into different rows Access Excel remove carriage return and remove line break Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspec...
More

Excel verify Text format and convert number to text

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 ...
More

Excel consolidate all Comments in the workbook

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 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     On Error Resume Next     Application.D...
More

Excel show all comments in worksheet

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 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 the Cell. Comment is hidden by default...
More

Excel verify Number format and convert Text to Number

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number. You may also want to read: Excel verify Text format and convert number to text Excel Cell Function to check Cell format, address, contents Excel verify Number format Number can be in two forms in Excel - Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell. By default, number in Text format aligns to the left while Number alig...
More

Excel Macro remove space in prefix and suffix

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 ...
More

Access Excel Function HKID Check Digit (last digit)

This Excel tutorial explains the algorithm of HKID Check Digit (last digit) and provide a custom Access Excel VBA Function to verify the Check Digit. (香港身份証最後號碼) You may also want to read: Access Excel Generate random HKID Hong Kong ID card number HKID Check Digit Hong Kong ID card number has a last digit with bracket called "Check Digit". Check Digit is a number to verify if the previous number and alphabet are correct. By understanding the algorithm of calculating Check Digit, you ...
More