Get the Actual Used Range in a Spreadsheet

In one of my posts, I wrote about the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to, when you press Ctrl+End.

Continue reading


Concatenate Unique Cells, with Text Formatting

A fellow struggler requested me to help him with a slightly beefed up Range Concatenate function. He needed to combine a bunch of numbers in a range; there were multiple entries of the same number, and he wanted a unique list; finally he needed to format the numbers.

Continue reading

Concatenate Range and Retain Formatting

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