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
Unit 9 – Excel Custom Sub Procedure
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