Select Similar Coloured Cells in a Range

Struggling to Excel

If you’d like to loop through all the cells in a sheet, it is very tempting to use something like the following:

Sub LoopThroughCells()

Dim rngCell As Range
For Each rngCell In Excel.ActiveSheet.Cells
	'Type your code here
Next rngCell

End Sub

Continue reading “Select Similar Coloured Cells in a Range”

Advertisements

Concatenate Range and Retain Formatting

Concatenate Range with Formats

In one of my previous posts, I wrote a function to concatenate the values stored in all the cells of a specified range. That function holds good, if you have a text stored in all the cells, and you just want to club them together. It fails if you have cells containing numeric values: dates, percentages, currencies or time. In another post, I wrote a function that returns a formatted string of the value stored in a cell, by automatically fetching the cell’s Number Format.

Continue reading “Concatenate Range and Retain Formatting”

An upgrade to the Text() function in Excel

Struggling to Excel

Many of us are familiar with the Text() function. It comes in handy when you set up excel to draft reports for you. It converts a numeric value stored in a cell to a string based on  the format specified by the user. The only thing I hate about that function is that you have to specify the format every time you use it. Another drawback is, the format of the text is not linked to the cell formatting of the cell that is being referred to. For instance, if you later decide to change the cell formatting of a cell, you also have to change the Special-Format-String argument in the Text() function.

Continue reading “An upgrade to the Text() function in Excel”

What are Volatile Functions in Excel

Struggling to Excel

Excel uses a three stage process to recalculate cells. It first draws a dependency tree, and then lists a calculation chain and then recalculates the cells. Check this link if you are interested in learning more about the recalculation process. Excel determines if a cell’s value depends on another, and decides the order in which they should be recalculated.

Continue reading “What are Volatile Functions in Excel”

Extract the Nth word in a String in Excel

Get Nth Word Excel Example

Visual Basics for Applications (VBA) allows users to build on the existing functionality of  Microsoft Excel. It comes with a strong set of inbuilt functions that are often not readily accessible from Excel’s interface. These functions have to be wrapped inside a user defined function to be usable in Excel. A classic example is the Split function.

Continue reading “Extract the Nth word in a String in Excel”

Concatenate a Range of Strings in Excel

Concatenate Range

Most of us have used the Concatenate() function in Excel. It is a neat function that saves you the trouble of keying in “&” between the range addresses. The only thing that would make this awesome would be the ability to concatenate an entire range. That would be way more useful than having to comma separate each cell that you want to concatenate.

Continue reading “Concatenate a Range of Strings in Excel”