Excel convert data from column to row

This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row). You may also want to read: Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet Excel convert data from column to row Different database have different structure. Some database put similar data in the same column with different rows, while some database put data in different columns. .Let's take an example. Typ...
More

Excel delete all pictures or delete all pictures in specific Cells

This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells. Excel delete all pictures When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to  delete all pictures at once. Let's say I want to copy the below table from a website. In the Country of Origin field...
More

Excel VBA unmerge columns automatically

This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column. You may also want to read: Excel VBA reformat merged cells to row Excel VBA separate line break data into different rows Excel VBA unmerge columns automatically For some systems, when you export a non-Excel report (e.g. PDF) to Excel format, some columns could be merged in order to fit the width of the original report layout. Even for Sharepoint, this formatting issue also happens. I...
More

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   ...
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...
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 ...
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 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 write a Macro to convert all CSV files in a folder to Excel. VBA Cod...
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 ...
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...
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...
More