Sometimes, you may need to dynamically insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This tip will help you quickly insert blank rows between multiple…
One must-have VBA skill is the ability to loop (or enumerate) through a range of cells. If you do any serious macro work in Excel, you will soon encounter the need to go through a range of cells one by one and perform some action. This basic macro shows you…
Creating Named Ranges Creating a named range via VBA is much less involved. You can directly define the Name property of the Range object: Sheets(“Sheet3”).Range(“A1:D10”).Name = “MyData” Selecting Named Ranges You simply pass the name of the range through the Range object. This allows you to select the range: Range(“MyData”).Select…
Description TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. Syntax TEXT(value,…
Description T function returns the text referred to by value. Syntax T(value) Parameters value Required. The value you want to test. Remarks If value is or refers to text, T returns value. If value does not refer to text, T returns “” (empty text). You do not generally need to…
Description VALUE function converts a text string that represents a number to a number. Syntax VALUE(text) Parameters Text Required. The text enclosed in quotation marks or a reference to a cell containing the text you want to convert. Remarks Text can be in any of the constant number, date, or…
Description RIGHT returns the last character or characters in a text string, based on the number of characters you specify. RIGHTB returns the last character or characters in a text string, based on the number of bytes you specify. What is the difference between RIGHT and RIGHTB? RIGHT is intended…
Description SUBSTITUTE function substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. Syntax SUBSTITUTE(text, old_text, new_text, [instance_num]) Parameters Text…