Category Archives: Excel

multi match vlookup

Get VLOOKUP Multiple Matches – Multi INDEX MATCH in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

The Excel VLOOKUP function by default allows you to find only a single match and will return the corresponding row of a selected column value. What if you want to find VLOOKUP multiple matches, not just the first one? In this post let us explore this more complicated scenario. Instead of VLOOKUP however we will use INDEX and MATCH.

Using the MATCH Function in Excel

To find the first MATCH of the “A” value in column B:B we use the following formula as shown on the image below:

See the scenario below:
Get single Excel MATCH

Finding multiple matches in Excel

Now say we want to find all matches of “A” in column B:B as seen below.
Get VLOOKUP Multiple Matches in Excel
Below the formulas in cells E2-E4. In E2 we find the corresponding row of the first “A”, then in subsequenty (E3-E4) we look for the rows of the next found “A”. You can drag this formula down as much times as needed.

VLOOKUP Multiple Matches

To do a multiple match VLOOKUP we simply need to expand on the above Multiple MATCH example and add the INDEX function like so:

Using VBA to do a VLOOKUP Multi Match

In case you want a more sophisticated approach to doing a multi match INDEX MATCH / VLOOKUP you can also use the VBA Dictionary to record all instances of all lookup values along with selected columns. A simple version of this approach can be found in my post about using VLOOKUP in VBA. Below, however, I expanded this example by using a VBA Collection inside the VBA Dictionary to store value associated with each match of every lookup value (basically creating a very simply tree-like structure).

Based on the “A1:B10” table above the VBA code below will create my dictionary dict object.

After creating the dictionary I can now print all values from column “A:A” for any value of column “B:B”:

Unhide Sheets in Excel

How to unhide sheets in Excel? Unhide all Sheets in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

  1. Right click on a the Worksheets tab
  2. Click the Unhide button in the opened tab
  3. Select the Worksheet you want to unhide and click Ok

Unhide hidden Sheets in Excel

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

Unhide all Sheets in Excel using VBA
Unhide all Sheets in Excel using VBA

Below the same code as above but spread across multiple lines. Let us run through this:

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:
Hidden worksheets Excel

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:
Manage Sheets UserForm

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

When executed this is how it will look like:
Unhide Multiple Sheets in Excel

Excel AutoFit Excel Rows and Columns

AutoFit Excel Rows and Columns

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Excel rows and columns sometimes needs to be expanded to fit their content. How to AutoFit in Excel? Fortunately Excel allows you to AutoFit Excel rows or Excel columns to its contents readjusting the height or width. In this post I will start with showing how to resize Excel rows and columns. Then I will demonstrate the AutoFit feature. Lastly I will show how using VBA we can AutoFit multiple rows of a table.

Resizing Excel Rows & Columns

Resizing Excel rows or columns is pretty straight forward. Simply grab the border of the row or column header and resize:
Excel Resize Column
Rarely would we want our columns to change their width automatically, however, Microsoft as built in a feature to make it easier for rows to re-adjust their size automatically based on the contents of cells. All we need to do is set the Wrap Text property:
Excel wrap text to avoid AutoFit

AutoFit Excel Rows & Columns

To AutoFit Excel row or column, instead of grabing the border of the a header row or column Double Left Click on the border. Excel will automatically adjust the row or column size.
Excel AutoFit Excel Column

AutoFit Excel Table with VBA

The above explains the basics of how to resize Excel rows or columns. However, data is dynamic – it is easier to review an entire table when rows are minimized, on the other hand to read entire contents of rows of data in Excel the rows need to be AutoFitted. Switching between both these states can be a drag hence I created a simple VBA Macro that either minimizes the size of my Excel tables or AutoFits them.
Example table with long text
Let us assume we have an Excel Table with at least 1 column which contents usually exceed the height of a single Excel row. This will look like in the image below.

What I started doing above was converting the Excel range (with my data table) to something called an Excel Table which will make it look like this:Example table with long text - converted to Excel Table

Now that we have our table let us add 2 VBA Macros to our VBA Project:

I added to my spreadsheet 2 buttons each connected to one of the above VBA Procedures. So conveniently now I can minimize my Excel rows or AutoFit all of them with just a single click like this:
Excel AutoFit VBA Macro

excel keyboard shortcuts

Microsoft Excel Shortcut Keys

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 4.00 out of 5)
Loading...

Below are common used Microsoft Excel Shortcut Keys grouped for depending on usage – from Excel Workbook shortcuts to VBA Macro Excel Shortcuts. Use the Table of Contents on the right to navigate.

Excel Workbook Shortcuts

Shortcuts Description
Create a new Workbook Ctrl+N
Save the Active Workbook Ctrl+S
Save As option for Active Workbook F12
Open a Workbook Ctrl+O
Close the Active Workbook Ctrl+W
Close current Window Ctrl+F4
Exit Excel Alt+F4
Previous Workbook Ctrl+Shift+Tab
Next Workbook Ctrl+F6
Recalculate all open Workbooks F9
Creates Names using either row or column labels Ctrl+Shift+F3
Move to the next pane F6
Move to previous pane Shift+F6
Move the Window Ctrl+F7
Open Help menu F1
Spell check selected text / Active Workbook F7
Restore Window size Ctrl+F5
Resize the Window Ctrl+F8
Minimize current Window Ctrl+F9
Maximize current Window Ctrl+F10

Excel Worksheet Shortcuts

Shortcuts Description
Creates a new Worksheet Alt+Shift+F1
Previous Worksheet Ctrl+Pg Dn
Next Worksheet Ctrl+Pg Up
Save the current Worksheet Alt+Shift+F2
Edit the selected cell F2
Edit comment on selected cell Shift+F2
Go to specific cell F5
Print Activesheet Ctrl+P
Open Excel Name Manager Ctrl+F3
Paste Name of created Named Range F3
Add to selection Shift+F8
Calculate Activesheet Shift+F9
Select all cells above selected Shift+Page Up
Select all cells below selected Shift+Page Down
Select all text to left of cursor Shift+Home
Select all text to right of cursor Shift+End
Select up one cell Shift+Up Arrow
Select down one cell Shift+Down Arrow
Select left by one character Shift+Left Arrow
Select right by one character Shift+Right Arrow
Newline in cell Alt+Enter

Copy/Paste, Find/Replace, Undo/Repeat

Shortcuts Description
Copy selected cells Ctrl+C
Find Ctrl+F
Find and Replace Ctrl+H
Paste Ctrl+V
Cut Ctrl+X
Repeat Ctrl+Y
Undo Ctrl+Z
Copy formula from cell above Ctrl+'
Copy value from cell above Ctrl+Shift+"
Paste clipboard Shift+Insert
Repeat last action (formatting included) F4
Enter Extend Mode F8

Formatting

Open Format settings Ctrl+1
Bold selected cells Ctrl+B
Italic selected cells Ctrl+I
Underline selected cells Ctrl+U
Strikethrough selected cells Ctrl+5
Insert hyperlink Ctrl+K
Underline selected cells Ctrl+U
Apply comma formatting Ctrl+Shift+!
Apply currency formatting Ctrl+Shift+$
Apply date formatting Ctrl+Shift+#
Apply percentage formatting Ctrl+Shift+%
Apply exponential formatting Ctrl+Shift+^
Border selected cells Ctrl+Shift+&
Remove border Ctrl+Shift+_
Change Font Family Ctrl+Shift+F
Change Font Size Ctrl+Shift+P

Selecting / Moving

Select Used Range of Active Worksheet Ctrl+A
Go to a certain cell/address Ctrl+G
Move to A1 Ctrl+Home
Move to last cell on Active Worksheet Ctrl+End
Switch between open Excel files Ctrl+Tab
Select array containing active cell (array formular) Ctrl+/
Select cells with static value in selection Ctrl+\
Select cells referenced by formula in selection Ctrl+[
Select cells referencing Active cell Ctrl+]
Select cells referenced selection Ctrl+Shift+{
Select cells which contain formulas that directly or indirectly reference the active cell Ctrl+Shift+}
Select cells in column not matching formula or value in Active cell Ctrl+Shift+|
Select entire Column Ctrl+Spacebar
Select entire Worksheet Ctrl+Shift+Spacebar
Select cells containing comments Ctrl+Shift+O

Data & Formulas

Enter current time Ctrl+Shift+:
Enter current date Ctrl+;
Insert argument names into a formula Ctrl+Shift+A
Create formula to sum all of the above cells Alt+=
Create chart from selected data F11
Open the “What’s This?” window Shift+F1
Open the Excel formula window Shift+F3
Insert chart Alt+F1

VBA/Macro Shortcuts

Shortcuts Description
Open the VBA Editor Alt+F11
Run your Sub/UserForm F5
Step into (run line by line) F8
Break code Ctrl+Break
Autocomplete / Intellisense drop-down Ctrl+Space
Delete current line of code Ctrl+y
Get screentip for current function Ctrl+i

Excel Key Tricks

Microsoft Excel Shortcut Key are very useful in saving you time to switch from your keboard to your mouse. Here are some of my favorite Excel Key tricks:

Quickly Navigating Excel Keys

Moving around your Excel Worksheet is one of the most important skills, saving you lots of mouse-time. Below are some of the key ones:
Move Excel Shortcuts
Hope this was useful!

vba delete files header

VBA Delete File – Deleting files using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

To delete a file using VBA you need to use the VBA Kill Function. The problem with the VBA Kill function however is that it will not check whether the file exists in the first place, and instead will throw and error.

VBA Deleting multiple files using Wildcards (*)

In case you have many files to delete in a single directory that match a certain sequence of characters in the name you can use Wildcards (*).

A Wildcard (*) replaces any number of characters (more than one) in the filename. An example below:

VBA Deleting files matching a Regular Expression

In some cases there are more complex rules defining the files we should delete. In such case we can create a new function that uses VBA Regular Expressions:

Having this functions we can delete files using patterns (be sure to read my VBA Regular Expression tutorial if you are new to Regex).

Let us use this example below.
vba delete file
I would like to delete files “1.txt” and “2.txt” while maintaining remaining files. Hence I can use the Regex pattern [0-9] which means any single numeric digit. Execution would look hence like this:

VBA Delete files recursively

In some cases you might not know if which directory the file you want to delete is and you have only a root directory. Traversing these folder manually is cumbersome. However with the help of my TraversePath procedure which I created in the postVBA Dir Function – How to traverse directories we can create our own RecursiveKill function:

Now using the same example from above let us assume we have the file 1.txt copied multiple times across our directory. We can easily delete all copies like this: