A few weeks ago I received this question from someone called LS: Hi I want to write a macro that counts the following – Number of cells with formula – Number of Rows with formula – Number of Columns with formula Please let me know if there is a way of doing it in Excel [...]

UPDATE June 13, 2008: Another alternative way to find the last row with data : Function LastRow() As Long Dim ix As Long ix = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ix End Function I did a simple test, and the function will return the correct last row position even when the data was [...]

Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only. Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we [...]

Auto run macro excel… Do you want to run an excel macros automatically when the file is open or close? Microsoft Excel provide us the opportunity to run a macro automatically every time we open or close the excel workbook file, using the event Workbook_Open and Workbook_BeforeClose. In the Microsoft Visual Basic Editor page, in the ThisWorkbook module, [...]

Imagine this condition; We are in the middle of doing something using Excel VBA macro, then we want the macro to automatically show the File Open dialog box to make us (user) able to choose another Excel file to be opened by Excel. Got the picture? Below is vba procedure that do exactly like that, [...]

Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary() Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As [...]

The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words. Function Acronym(Words As Variant) As String Dim aWord() As String, ix As Integer aWord = Split(Words.Value, ” “) For ix = 0 To UBound(aWord) Acronym = Acronym [...]