Erlandsen Data ConsultingAccording to the Excel Developer Reference for Office 2007 documentation, or well hidden in the built-in the VBA help file, you should be able to delete duplicate entries from a worksheet range using the RemoveDuplicates method like this: ActiveSheet.Range(“A1:F100″).RemoveDuplicates This method is new in Excel 2007 and will not work in older Excel versions. It [...]
Both of the function examples below can be very useful when you create a solution that is depending on input from a special worksheet. Instead of bothering the user by asking about the worksheet and workbook that contains the data source, you can use the functions below to find that special worksheet, as long as [...]
Both of the function examples below can be very useful when you create a solution that is depending on input from a special worksheet. Instead of bothering the user by asking about the worksheet and workbook that contains the data source, you can use the functions below to find that special worksheet, as long as [...]
Normally when you want to delete rows from a worksheet you would create a simple loop that would check if a row should be deleted or not, and then delete each row separately. This works fine as long as you have a small data set or don’t have to delete many rows. Sub DeleteRowsExample() ‘ [...]
The procedure below can be used to protect all cells containing formulas in a worksheet. Sub ProtectFormulasInWS(ws As Worksheet) ‘ ws must be unprotected before running this procedure ‘ protect ws afterwards for the protection to take effect Dim objRange As Range If ws Is Nothing Then Exit Sub With ws If .ProtectContents Then Exit [...]
Have you ever forgotten your workbook protection passwords? This tool has been available from this site since the days of Excel version 5 and has helped thousands recover their work, for free. This tool will in a relative short time find a password capable of unprotecting the locked workbook and/or worksheets (this password will probably [...]
This workbook shows how you can insert subtotals at the bottom of each page in a worksheet before you print it. Click here to download this file. Updated: 2006-05-18 & 2012-04-11 Requires: XL5 File size: 50 kB
As long as you are using the default/normal font this problem has a simple solution: ActiveCell.ColumnWidth will return a number that represents the average character count that will fit inside the column width, assuming you are using the font defined in the Normal style. If you are using a different font you can use the [...]
With the macro below it is possible to compare the content of two worksheets. The result is displayed in a new workbook listing all cell differences. Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet) Dim r As Long, c As Integer Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer Dim maxR [...]
The custom function below can be used to return a collection of the page ranges in a worksheet. This can be useful when you need to be able to target a spesific page (e.g. when formatting or copying). Function GetPageRangeAddresses(ws As Worksheet) As Collection ‘ returns a collection containing the page range addresses of a [...]