Excel VBA Import CSV into Excel using Workbooks.OpenText Method

This Excel VBA tutorial explains how to import CSV into Excel automatically using Workbooks.OpenText Method. You may select different delimiters such as Tab, semicolon, comma, space. You may also want to read: Import Chinese CSV to Excel Excel VBA convert CSV to Excel Excel VBA Import CSV into Excel using Workbooks.OpenText Method In Excel workbook, you can manually import a CSV file into Excel (Data > From Text / CSV). However, you have to select some options in advance such as delimi...
More

Excel VBA Convert Text in Columns and Rows into Matrix Table

This Excel VBA tutorial explains how to convert text in columns and rows into Matrix Table. The Matrix table will display text instead of aggregated numbers. You may also want to read: Access Crosstab Query Excel VBA Convert Text in Columns and Rows into Matrix Table Using Excel Pivot Table, it is easy convert data into a Matrix Table with the help of Aggregate Functions such as Count, Sum, standard deviation. The information in the Matrix Table is displayed in aggregated numbers. Below ...
More

Excel VBA search text in multiple Workbooks in folder

This Excel VBA tutorial explains how to search text in multiple Workbooks in a folder and subfolders, and display the result in a summary page, including which workbook, which worksheet, and which Cell contains the text. You may also want to read: Excel VBA INSTR Function Excel loop workbooks in folders and subfolders with FSO Excel VBA search text in multiple Workbooks in folder Suppose you have multiple workbooks in a folder, and you want to know if any workbook contains a text yo...
More

Excel VBA copy contents of protected worksheet

This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet. You may also want to read: Excel VBA protect worksheet with password Excel VBA hide worksheet with password (xlVeryHidden) Copy contents of protected worksheet Some authors would protect worksheets with password so that other people cannot modify and not even able to select the cells. I usually do it when I setup templates for users to fill in the data but I don't...
More

Excel VBA convert date period to date row

This Excel VBA tutorial explains how to convert date period to date row. Excel VBA convert date period into date row Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period Begin Date End Date 1/1/2018 1/3/2018 However some system store one date per row. Begin Date End Date 1/1/2018 1/1/2018 2/1/2018 2/1/2018 3/1/2018 1/3/2018 ...
More

Excel VBA filter value then copy data to new worksheet

This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data to new workbook. You may also want to read: Excel VBA copy each worksheet to new workbook Access VBA auto generate mass report by group to Excel Filter value then copy data to new worksheet I find it very difficult to come up with a suitable title and key words for this post. I will try to describe what this Macro does with an example. Suppose you have a staf...
More

Access Excel VBA sort Array items

This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order. You may also want to read: Use VBA Excel Function to sort data in ascending order Access Excel VBA sort Array items In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, Cat...).  If you store a number list, you may want to sort them in ascending order (100, 200, 400, 70...
More

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