Excel VBA combine rows into cell

Posted on January 14, 2017 by Wyman W

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

Excel VBA find all Cells contain Named Range

Posted on December 26, 2016December 26, 2016 by Wyman W

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

Excel VBA Named Range Properties

Posted on December 25, 2016 by Wyman W

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

Excel VBA select multiple files in dialog

Posted on December 21, 2016 by Wyman W

This Excel VBA tutorial explains how to select multiple files in dialog using Application.GetOpenFileName. Excel VBA select file in dialog In many applications, you would see a "browse" button where you can select files for the application to process. In Excel VBA, you can create a Macro to open the browse dialog. After you select the target file, the file path is stored in a VBA Array, then you can loop through the array to get items, and then do whatever you like with the file. The...

Excel VBA change partial text color in Cell

Posted on December 10, 2016September 13, 2017 by Wyman W

This Excel VBA tutorial explains how to change partial text color in a Cell using Macro. You may also want to read: Access Excel remove text in brackets Change partial text color in spreadsheet In Excel spreadsheet, you can change part of the text in a Cell by highlighting the text and then change the font color. Suppose you want to change the text World in Cell A1 to red color. First, highlight the text. Then select red color in the font color option. Change p...

Excel VBA convert cell contents into table

Posted on August 5, 2016 by Wyman W

This Excel VBA tutorial explains how to convert cell contents into table using Exel VBA. Excel VBA convert cell contents into table Yesterday when I tried to copy a table from a website to Excel, all the table contents are pasted into a single cell instead of copying the whole table. I believe there are workarounds to copy table but since this is an Excel blog, I want to write a Macro to convert cell contents back into a table. Previously I also wrote a post to convert cell contents into ro...

Excel automatically refresh pivot table

Posted on July 19, 2016 by Wyman W

This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh pivot table. Method 1 - Automatically refresh pivot table when opening the file This is the simple...

Access Excel VBA delete all workbooks in folder

Posted on June 2, 2016 by Wyman W

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA. Access Excel VBA delete files in folder Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete files, you can use FSO Fil...

Access Excel VBA delete folder if folder exists

Posted on June 1, 2016June 2, 2016 by Wyman W

This tutorial explains how to use Access / Excel VBA to delete folder if folder exists already. Access Excel VBA delete folder if folder exists Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete a folder, you can u...

Excel VBA delimit Cell value into rows

Posted on May 27, 2016August 5, 2016 by Wyman W

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outlook into Excel. When I paste to Excel 2013, all email addresses are pasted to a singl...