Category Archives: MS Office

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:

VBA Tricks

VBA Tips and Tricks

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

Instant Print in the Immediate Window

Use the “?” preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below:

VBA Trick Use question mark for instant Debug Print
VBA Trick Use question mark for instant Debug Print

This is a VBA Trick I was not aware for a lot time but saves a lot of time when debugging code or trying to evaluate a single line or bit of code without needing to debug a whole functions or procedure.

If you are not familiar with the Immediate Window read my VBE Project tutorial

Evaluate formulas shortcut

One of my favorite VBA Trick is using the evaluate function using square brackets “[]”. If you want to read more read my VBA Evaluate article.

Evaluate works both on:

  • Evaluation of Named Ranges
  • Evaluation of Excel Worksheet Functions

Evaluating Named Ranges in VBA

Here’s an example of Named Range evaluation in VBA:

VBA Trick: VBA Evaluate Ramed Range
VBA Trick: VBA Evaluate Ramed Range

Evaluating Worksheet Functions in VBA

Here’s an example of Worksheet Function evaluation in VBA:

VBA Trick VBA Evaluate Worksheet Function
VBA Trick VBA Evaluate Worksheet Function

This is one of the most useful trick as VBA does not allow you to otherwise easily do a lookup of data is a worsheet.

Remeber to always use the “,” list separator when defining the arguments as VBA is not not dependent on your system country settings even if you separate arguments in regular Excel worksheet functions.

Use VBA. to view all VBA Built in functions and constants

Often you stumble not being able to remind yourself of that one useful VBA Function and need to search the function online. A great VBA Trick is to type in “VBA.” and then you will be provided with a list of available VBA Functions and Constants.

VBA Trick Use VBA to list all built in functions and constants
VBA Trick Use VBA to list all built in functions and constants

User Defined Functions

Excel has a multitude of functions, half of which you probably don’t know. However, what happens when you lack a particular function or want to save time bundling up common functions? Let me introduce you to the little known world of User Defined Functions (UDF).

Simple UDF

A simple UDF can be found below:
VBA Trick Simple UDF

Advanced UDF

Let us not try a more advanced UDF showing how powerful it can be. First I will create a simple table:

VBA Trick UDF Table
VBA Trick UDF Table

Now what I need is to somehow get a number of matches or these records against various criteria e.g. sex, sales, age, country. One way would be to go for a Pivot Table but sometimes a simple UDF can be more useful especially if we want to embed this value in your calculations.

So I will create a UDF that takes these arguments and provides the number of matches:

Now we can use the UDF Function in Excel:

VBA Trick Complex UDF
VBA Trick Complex UDF

Speeding up your VBA Macros

Your macro running slow? A lot of CPU overhead goes into updating your screen in case and recalculations of formulas impacted by your macros. With a simple VBA Trick you can turn off ScreenUpdating and Calculations until your macro is done.

What to do when your macros needs to run for a long time and you need to update your screen every now and then? Use the DoEvents procedure to refresh your screen only in selected intervals.
Read here how learn of more ways of optimizing your VBA performance.