This Access Excel VBA tutorial explains how to convert number to English words in VBA.
Access Excel VBA convert number to English words
I want to share a custom Function I found in Microsoft Support website, which is to convert number to English words using Excel VBA (also applicable to Access). I want to highlight that the article is unlike other articles written by other contributors, this article has no author and I believe this Function was created by the Microsoft engineers, so this custo...
Read More
Chapter 9 – Excel VBA
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...
Read More
Excel VBA Range PasteSpecial Method
This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values.
Excel VBA Range PasteSpecial Method
In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula.
In Excel VBA, Paste Speical is done through Range.PasteSpecial Method.
Syntax of Range.PasteSpecial Method
Range.PasteSpecial(Paste, Operation, SkipBlanks, Tran...
Read More
Excel VBA set print area
This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA.
Excel VBA set print area
To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area
To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property.
For example, in order to set Print Area A1:N21, we can write
Public Sub printArea()
Active...
Read More
Excel VBA freeze panes using FreezePanes property
This Excel tutorial explains how to freeze panes in Excel spreadsheet and freeze panes in Excel VBA using FreezePanes property.
Freeze Panes in Excel spreadsheet
To explain how to freeze panes in Excel VBA, first I have to explain how to freeze panes in Excel spreadsheet.
Freeze Row
The most common kind of freeze panes is to freeze the first row as it contains the header of the column. So that when you scroll down the spreadsheet, you can still see the header in row 1.
Highlight row 2, na...
Read 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...
Read 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
...
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 declare Global Variable and variable scope
This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable.
You may also want to read:
Function, Sub, Method, Property
Difference between Dim and Set
Scope of local variable / global variable / private variable
Procedure Level
When you declare a variable inside a Sub Procedure or Function (local variable), the variable can only be used within them.
For example, in Sub Procedure1, you have declared...
Read More