Excel VBA Macro

Sum Unique/Distinct Values in Excel

Poer @ Excel VBA/Macro — Tue, 20 Oct 2009 07:29:05 +0000

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 have [...]

Post from Excel VBA Macro

Sum Unique/Distinct Values in Excel

Excel Extract Cell Comments

Poer @ Excel VBA/Macro — Fri, 19 Jun 2009 07:11:52 +0000

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, [...]

Post from Excel VBA Macro

Excel Extract Cell Comments

How to get an Acronym using Excel VBA?

Poer @ Excel VBA/Macro — Wed, 17 Jun 2009 07:25:04 +0000

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 [...]

Post from Excel VBA Macro

How to get an Acronym using Excel VBA?

Get Position of Last Column Containing Data with Excel VBA

Poer @ Excel VBA/Macro — Sat, 13 Jun 2009 03:38:28 +0000

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 [...]

Post from Excel VBA Macro

Get Position of Last Column Containing Data with Excel VBA

Find the Last Day of any Month

Poer @ Excel VBA/Macro — Tue, 09 Jun 2009 03:42:11 +0000

Simple Microsoft Excel formula to find the last day of any given month. ' =DATE(YEAR(A1),MONTH(A1)+1,0) ' Post from Excel VBA MacroFind the Last Day of any Month

Post from Excel VBA Macro

Find the Last Day of any Month

Schedules to Refresh Data Automatically using VBA Macro

Poer @ Excel VBA/Macro — Mon, 08 Jun 2009 16:02:37 +0000

I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook. Below is a sample of how we can do this in Microsoft Excel *just an outline*. The logic is simple, when we open the [...]

Post from Excel VBA Macro

Schedules to Refresh Data Automatically using VBA Macro

Excel VBA Add Set of Worksheets Automatically

Poer @ Excel VBA/Macro — Mon, 08 Jun 2009 02:53:14 +0000

There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base. Here a simple example of how [...]

Post from Excel VBA Macro

Excel VBA Add Set of Worksheets Automatically

Auto Format Excel Cells with Error Value

Poer @ Excel VBA/Macro — Tue, 02 Jun 2009 16:38:18 +0000

With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells. In this example, background color of the cell that contains the error value will automatically [...]

Post from Excel VBA Macro

Auto Format Excel Cells with Error Value

Auto Expanding VLOOKUP Table Array

Poer @ Excel VBA/Macro — Thu, 02 Apr 2009 11:06:53 +0000

Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data. As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:D15 column no 2 is like this: =VLOOKUP(A1,B1:D15,2) With parameters such as the value we looking [...]

Post from Excel VBA Macro

Auto Expanding VLOOKUP Table Array

VBA Delete Excel Rows Based on Certain Date

Poer @ Excel VBA/Macro — Sat, 31 Jan 2009 14:05:57 +0000

Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from the [...]

Post from Excel VBA Macro

VBA Delete Excel Rows Based on Certain Date