Almir's blog

Almir's picture

Export all modules to another workbook

'Use this code to export all modules from Personal.xlsb file to another workbook. Prior to running macro, open target workbook and change its name in the code accordingly.
Sub ExportImportModule()
For Each Module In Workbooks("Personal.xlsb").VBProject.VBComponents
If <> "ExportImportModule" Then
Module.Export ("c:\temp\" & & ".bas")
Workbooks("TargetWorkbook.xlsm").VBProject.VBComponents.Import ("c:\temp\" & & ".bas")
End If
Next Module
MsgBox "All objects (modules, forms and classes) were copied!"

Almir's picture

Solution to "Macro code that will repeat x number of time based upon a cell in my excel spreadsheet"

This is solution to post at: Example file is attached.

Macro code that will repeat x number of time based upon a cell in my excel spreadsheet
Almir's picture

Solution to Budgeting

Almir's picture

Example of calculated pivot field to compare two data sets

Almir's picture

Calculate Easter Date

Thanks "Mr Spreadsheet" John Walkenbach for this formula.

Calculate Easter Date
Almir's picture

Add a custom command to your right mouse-click menu

When using particular macro often, it is handy to have it on right mouse-click. In this example there is a simple macro "Show Date and Time", and it was added to right mouse-click menu.

Supposing you have a macro called "DateAndTime" in a "Module1", copy code from attached .TXT file to the "Workbook" part in VBE.

With slight modification you can add more commands to the right mouse-click menu. It was explained in the comments.

If you want custom commands available on right mouse-click, regardless of the file you work on, copy the code to your "Personal" macro file.

Add a custom command to your right mouse-click
Almir's picture

An Example of Bracketed Column Name

This is an example based on post at:

Bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[meals])" means "SUM of 'Meals' column", where "Meals" is a column within Data Table.

Maybe "Sum" cell was copied away from a Data Table, and that is why it looked strange.

Bracketed Column Name in Data Table
Almir's picture

Create Index page with hyperlinks to sheets

This is solution to: "Hyperlink Macro that converts a list of sheet names to hyperlinks to the sheets", at:

To create an index page with hyperlinks to all sheets in a workbook:

1. First, insert a new sheet and name it "Index"
2. In VBE (Alt+F11), select "Index" sheet and paste the code from the attached .txt file into the right pane:
3. Run Macro "CreateIndex"

Create Index page with hyperlinks to all sheets in a workbook
Almir's picture

Solution to Count Before Change

Solution to count before change at:

I guess that you want to count number of occurences of "Yes" and "No" in a block of consequtive occurences (i.e. before it changes from "Yes" to "No" or from "No" to "Yes"), right?

Having your data in column A, enter 1 in B2 and this formula in B3: =IF(A3=A2,B2+1,1).

Thus, when "Yes" turns into "No" or vice versa, counter resets to 1. If it is same as the row above, it increments counter to + 1.

Example file attached.

Please, let us know if this is what you need.

Count  before change
Almir's picture

Solution to Custom Date Request

This is solution to request posted at:

1. Enter 2 Mar and 8 Mar 2014 into A1 and B1 as dates
2. Format them like: [$-409]Ddd d mmm yyyy"
2. Enter this formula in A2 and fill it down:

=PROPER(TEXT($A$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy")) &" to " & PROPER(TEXT($B$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy"))

Example file attached.

Custom Date
Almir's picture

List Unique Items in a Range

If you need a list of distinct/unique items from the range (column A) where items appear more than once, select a range of the same size (column C) and enter this as an array formula (press CTRL+SHIFT+ENTER):

=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:" & ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:" & ROWS(Data))))),"")

Where "Data" is a named range containing original list (column A).

Named range is not mandatory, but I recommend you create it, so formula is easier to handle.

List unique items in a range
Almir's picture

Solution to "How to display values of 2 variables which range from 4 to 15 in 1 message window using MsgBox?"

Solution to How to display values of 2 variables which range from 4 to 15 in 1 message window using MsgBox? at: This generates a pair of random numbers from defined range, so two numbers in a pair are always different. Thanks to J. E. McGimpsey for this code.

Check example file attached.

Almir's picture

Solution to "ListBox change calls a different macros"

Regarding: "" and: "": How change in listbox value calls a different macro.

Create ActiveX ListBox and create a list of macros, like in the example file attached.

Put this code into your worksheet part:

Private Sub ListBox1_Click()

If ListBox1 = "Macro1" Then
Call Macro1
End If

If ListBox1 = "Macro2" Then
Call Macro2
End If

If ListBox1 = "Macro3" Then
Call Macro3
End If

If ListBox1 = "Macro4" Then

ListBox change calls different macro
Almir's picture

Solution to "add incremental numbers in a filtered column" post

This is reply to the post:

Having your column filtered, you probably insert incremental numbers in different column, right?
1. Filter your data by column A
2. Enter the first value in column B, for example: 100
2. Select the remaining cells in column B to be filled
3. Press F5 and choose "Special", select "Visible Cells Only"
4. In the first empty cell in column B type = (select cell with 100 entered ) + 1

You will get 100, 101, 102 etc. in column B, only in visible rows.

Screenshot attached.

Fill in incremental numbers in a filtered list
Almir's picture

Environment Variables Listing

If you need information on computer name, username logged on, Operating System , domain name, "Windows" folder, "Application Data" folder, number and type of processors or alike, this is for you. This macro lists all environment variables in column A of active sheet, starting from cell A1.

Example file attached.

Environment Variables Listing
Almir's picture

Solution to Return top 5 values based on a criterion

Solution for:

In brief: you need Category List re-arranged, additional column in "Average Rank" sheet and named ranges for newly inserted column and all months respectively.

In detail: I re-arranged Category List and made a new one on sheet "Category_New".
Then I inserted a column on sheet "Average Rank" containing type (Composite, Standalone etc.).
Named ranges were created for march and april 2013 (containing values).

Finally, array formula calculates Top 5. Example for February 2013 - "Composite":

Almir's picture

Formula to reverse order of name and surname in the same cell

You might get a list with names and surnames stored in the same cell, and you need to reverse their order (from "Name Surname" to "Surname Name"). Most common way to achieve this is through MID/LEFT/RIGHT functions or "Text to Columns" command. But that is only the first step. Next step is to concatenate cells in reverse order.

Here is a formula to get it at once, and it also works with two surnames and two names, separated by space (Caetano Gonzalez) or dash (Mary-Jane).

Almir's picture

Insert images into respective cells according to filepaths stored in a range

Here is a macro to insert image files into respective cells, according to image filenames or full filepaths.
File names/paths are stored in "D" column, and pictures are inserted in "A" column, in the same row.

If you have only the list of file names, use the first macro ("Short Name"). Change the macro so it refers to the folder where image files are stored.

If you have a list of image files as full path+filename, use the second macro ("Full Path").

I recommend using the second method.

Insert images into cells according to file names stored in a range
Almir's picture

Formula to check if cell value in a range exists or does not exist in another range

Although there is a more elegant way created by Nick at:, here is one more way.

Let's suppose there are two ranges with some common values. In order to find what values from one range exist in another range and vice versa, use the following formula in the cell beside A2 cell in the first range:

=IF(COUNTIF($D$2:$D$10;A2)>0;"Exists in Range Two";"Nope")

Compare two ranges
Almir's picture

Easier way to handle your formulae: Make them look like a programming code

If you don't know it already, you can write your formulae (the complex ones) in a way similar to one that programmers use to make their code more legible. It is particularly usefull when creating complex nested formulae (formula within formula), as well as the logical ones, like OR, AND and alike. Why? Well, it is very easy to get lost in long formula: Where is the beginning? What is condition? Where to put a parenthesis? Where did I make a mistake? etc.

Look at the following formula and try to figure out what it does:


Same formula with indentations and explanations within
Almir's picture

Excel "smart list": drop-down list dependent upon user's choice of another drop-down list

When data entry is strictly defined, it is nice to have data validation list in place, so entries are uniform. Furthermore, a nice way to make data entry faster is to have "smart lists", or dependant lists. What does this mean?

When you choose an option from drop-down list in a column, you need only possible options listed in another column, based on the first choice. Let's look at a simple example with dogs and cats.

Example file is attached.

Dependent drop-down lists
Almir's picture

Timesaver Tip: Calculate only selected range of cells

Sometimes, while working on large workbooks with many complex and slow formulas we set calculation to manulal instead of automatic. From time to time, one needs to calculate only a range of cells. However, pressing F9 (Calculate) calculates all cells, and that can often be time-consuming.

To calculate the selected range of cells only (and to save significant amount of time) when calculation is set to manual, select a range of cells and use a simple macro, like this:

Sub CalculateSelection()
Application.screenupdating = False ' speed up execution by preventing screen flickering
Selection.Calculate ' calculating selected cells
Application.screenupdating = True ' reverting to default settings
End Sub

Almir's picture

Excel macro to change selected range UPPERCASE, lowercase or Proper Case - without additional column and functions

This macro changes case of selected range. No additional column or UPPER/LOWER/PROPER function necessary.
When you select a range of cells or single cell, run macro and - when prompted - enter "L", "U" or "P" (lower, upper, proper).
Selected cells contents is changed accordingly.

Almir's picture

INDEX/MATCH-based function, no need for complex formula

The first version was criticised with reason. Meanwhile, I used INDEX/MATCH logic to create a function similar to VLOOKUP.

If you are fed up with VLOOKUP inability to work leftwards, here is add-in called Two_Way_Lookup. It returns value from the same row, no matter where it is stored: in the column to the right or to the left from lookup value.

Public Function TWO_WAY_VLOOKUP(Lookup_Value As Variant, Reference_Column As Range, Result_Column As Range) TWO_WAY_VLOOKUP=WorksheetFunction.Index(Result_Column,WorksheetFunction.Match(Lookup_Value, Reference_Column, 0), 1)
End Function

It is based on INDEX/MATCH. This is for users who don't like nesting formulas. It works like this:

Almir's picture

Secondary Axis: How to show two data series on different scales.

Secondary Axis tutorial explains how to show two data series on different scales.
To see it, please, follow this link:

Syndicate content