Excel delete duplicated data in consecutive rows

This Excel tutorial explains how to delete duplicated data in consecutive rows (delete the value, not delete row). You may also want to read: Excel delete blank rows Remove duplicates in text Excel delete duplicated data in consecutive rows In conventional Pivot Table layout (Tabular), all data are grouped together and duplicated data would not repeat. Division Department Div1 Dept1 Div2 Dept2 Dept3 Dept4 Div3 Dept5 Dept6   ...
Read More

Excel VBA use Inputbox to select options

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE. Excel VBA use Inputbox to select options In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case. In the below example, I create three options in the Inputbox for users to select: 1. Mary 2. May 3. Susan If users select something else, a...
Read More

Excel delete all comments in Workbook

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA. You may also want to read: Excel show all comments in worksheet Excel consolidate all Comments in the workbook Excel - delete all Comments in Worksheet In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right click on any selected Cells > select Delete Comment.   If you are not certain where ...
Read More

Excel VBA convert CSV to Excel

This Excel tutorial explains how to convert CSV to Excel in Excel VBA. You may also want to read: Access Excel FSO File Methods Import Chinese CSV to Excel Excel VBA Import CSV into Excel using Workbooks.OpenText Method Convert CSV to Excel in Excel VBA CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. This tutorial demonstrates how to wr...
Read More

Excel VBA copy each worksheet to new workbook

This Excel tutorial explains how to copy each worksheet to new workbook in Excel VBA. You may also want to read: VBA Worksheets.Copy Method to copy worksheet VBA copy workbook or file Excel VBA copy each worksheet to new workbook Suppose you have a workbook that contains multiple worksheets, now you want to copy each worksheet to a new workbook. In this post, I will demonstrate how to do it in Macro. Sub cpyWS()     Application.ScreenUpdating = False      Application.DisplayAlerts ...
Read More

Access Excel VBA rename workbook or file

This Access Excel VBA tutorial explains how to rename workbook or file in VBA using Name As. Access Excel VBA rename workbook or file It is very easy to rename a workbook or file in VBA using the below syntax. Name and As are the keywords. Name currentFilePath As newFilePath For example, suppose we have a file called test.xlsx in Desktop, and we want to rename it as test2.xlsx, run the below Procedure Public Sub renameWorkbook()     Name "C:\Users\WYMAN\Desktop\test.xlsx" As "C:\Users\WYMA...
Read More

Access Excel VBA copy workbook or file

This Access Excel VBA tutorial explains how to copy workbook or file. You may also want to read: FSO File Methods Worksheets.Copy Method to copy worksheet Access Excel VBA copy workbook or file There are two common ways to copy workbook or file FSO.CopyFile Method FileCopy Function I have explained how to use different FSO Methods in my previous post, click to see more details. Using FSO.CopyFile Method to copy workbook You should be able to run FSO in Excel 2013. If you fa...
Read More

Excel VBA combine rows into cell

This Excel VBA tutorial explains how to combine rows into Cell. You may also want to read: Separate line break data into different rows Delimit Cell value into rows Excel VBA combine rows into cell In my previous post, I demonstrated how to delimit a Cell into separate rows,  one example is that you copy a email list and you want to put each email address into different rows. From: To: In this  post I will do a reverse version, I will combine rows back into cell, so t...
Read More

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

Excel VBA Named Range Properties

This Excel VBA tutorial explains some common Properties of Name Object (Named Range), and demonstrate how to replace named Range with formula. You may also want to read: Excel find all external links and broken links in workbook Excel VBA Named Range Properties In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) In the below screenshot, I have defined a Named Range called testNameRng, which refers to Cell A1 in Worksheet1. Currently A1 conta...
Read More