AutoGrid - Office Automation Blog , VBA Programming , Macros , Excel , Open Office , Tips, Tutorials

Highlight Duplicates in Excel

vinod — Mon, 18 May 2009 12:21:58 -0700

sometime we have to highlight Duplicates in excel and later work on them rather then deleting them using option " Remove Duplicates " .
here we will use " Conditional Formatting " for finding-out duplicates .

Application Volatile

ravisagar — Tue, 12 May 2009 00:39:16 -0700

In our earlier post we showed one example of User Defined Function or Custom function that one can write to perform operations that are not provided by in build functions in the excel. Though there is one limitation of User Defined Function that the formula will not automatically recalculate the values whenever the cells are updated.

Function to find Unique Items in a list

ravisagar — Mon, 11 May 2009 20:58:57 -0700

It happens many times when you are writing a Macro and you need to find the unique items out of the a range of items. Suppose you have an excel sheet that has the employee ids of you the people working in a company but the list has duplicate entries. Though there is a feature in excel where you can just display the unique name but to do the same using Macro you need to write a code.

Use Excel as a Pocket Calculator

vinod — Mon, 11 May 2009 15:20:54 -0700

Sometimes you need to calculate a value before you enter it into your worksheet. Before you reach for your pocket calculator, you may like to know that Excel lets you enter a formula in a cell, and then use the result in that same cell. This way, the formula disappears and you're left with the result of the calculated value. This will not only save your time but also make you more efficient in excel because you won't need to open a separate application for finding out simple multiplications or other calculations.

Refresh all Pivots in the Excel workbook

ravisagar — Mon, 11 May 2009 02:28:46 -0700

As a part of my work I have to make many complex reports with so many Pivot tables to represent the data into various formats. On an average the reports that I work with has at least four to five pivot tables in a single workbook. Pivots work great but you have to manually refresh all the pivots whenever you update the data.

Change color of cell content using a shortcut

ravisagar — Sun, 10 May 2009 23:24:42 -0700

It is frustrating to format the report in excel. Most of the companies follows certain guidelines to present the report in a specified format like column headings should be in grey background color, the font size should be 8 etc. Changing the content color of the cell is done too often. To work faster you can use a shortcut to change the color of any cell or group of cells that you have selected.

Procedures in VBA

ravisagar — Sun, 10 May 2009 21:24:48 -0700

A procedure holds a group of VBA statements that accomplishes a desired Task. A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor. A module can hold any number of procedures. You have a number of ways to call, or execute, procedures. A procedure is executed from beginning to end (but it can also be ended prematurely).

Hello VBA World!

ravisagar — Sun, 10 May 2009 21:12:02 -0700

Ok so we have learned how to record a Macro and we discussed couple of examples where we recorded the macros. As we discussed in our earlier blog that for modifying our recorded Macros or to write our own Macro from beginning we need to use VBA Editor.

How to use VBA Editor

ravisagar — Sun, 10 May 2009 21:06:56 -0700

So you have recorded your first Macro in Excel VBA but most of the times you want to do something more that cannot be achieved with just recording and for customizing the Macro you need to Modify the VBA Code. VBA Editor is just for that, modifying and writing your own code.

Record a Macro to Merge/DeMerge cells in Excel

ravisagar — Sun, 10 May 2009 00:36:31 -0700

In our earlier posts we discussed how to record a Macro and we discussed one example to record a macro to AutoFit. Lets take one more example to create a Macro to Merge and De-Merge the selected cells quickly, which is otherwise a long procedure.