Update: Sorter version of the VBA function: Function Number2Char(c As Integer) As String Number2Char = Split(Cells(1, c).Address, “$”)(1) End Function Courtesy of The Plaid Cow. OR Function Number2Char(ByVal vNumber) Dim addr As String If vNumber 256 Then Exit Function addr = Range(“A1″).Offset(0, vNumber – 1).Address Number2Char = Replace(Replace(addr, “$”, “”), Range(addr).Row, “”) End Function Courtesy [...]
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel. The VBA code is the exact replica of the code to get the last row, only [...]
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 [...]
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 [...]
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 [...]
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, [...]
The Excel VBA macro below will create a new Excel Worksheet called ‘RawData’ or we can use msgbox to ask for the Worksheet name if needed. If there is already a Worksheet called RawData, user will be ask whether they want to use the old Worksheet and cancel new Worksheet creation, or delete the old [...]