Frequently used ms-excel shortcut keys to speed up work

Hello readers, Now Its around 7 years of experience I am having in automation and reporting. I would like to share some frequently used shortcut keys in MS-Excel:

Shortcut key to  copy cell/ text/range/any excel object
- Ctrl+c (Control and c)

Shortcut key to  paste cell/ text/range/any excel object
- Ctrl+v (Control and v)

Shortcut key to fill value or formula from cell at top
 - Ctrl+d (Control and d)

Shortcut key to fill value or formula from left cell to current cell
- Ctrl+r (Control and r)

Shortcut key to go to next filled cell
-Ctrl+arrow Key (Control and any arrow key)

Shortcut key to go to next filled cell
-Ctrl+arrow Key (Control and any arrow key)

Shortcut key to select all cells from current to next filled cell
-Ctrl+shift+arrow Key (Control and shift and any arrow key)

Shortcut key to go to cell "A1"
-Ctrl+Home (Control and Home key)

Shortcut key to select table which have active cell
-Ctrl+a (Control. and a)

Shortcut key to entire sheet
-Ctrl+a Ctrl+a (control and a twice)

Shortcut key to  apply or remove filter
-Ctrl+dff (Control and d then ff)

Shortcut key to toggle between formula and value entered in a cell.
- Ctrl+~ (Control plus tilde)

Shortcut key to delete selected rows/columns/cells:
- Ctrl+ - ( Control and minus)

Shortcut key to add rows/column/cells:
- Ctrl++  (Control and Plus key)

Shortcut key to rename sheet(Tab):
- Alt+ohr (Alt and "o" then "hr")

Shortcut key to delete sheet (Tab):
- Alt+hds (Alt and "h" then "ds")


Above shortcut keys will help you to increase your speed of work. Apart from these shortcut keys you should know basic functionality of excel.

Fixing insert object error for activeX objects in VBA macros

Some day in MS-Excel if you find your activeX controls are not working which were working fine till yesterday. So don't worry try below fixes for such issues.

Goto below mentioned paths and delete .exd files and reopen excel. 

• %appdata%\microsoft\forms
• %temp%\excel8.0
• %temp%\word8.0
• %temp%\PPT11.0
• %temp%\vbe

If still issue persist then goto Run type appwiz.cpl and click on view install updates from left navigation bar. And remove if there are any new updates have been installed for MS-Office recently. 

Hope this will help you to resolve issue.

You can also download .msi file to fix this issue, You will get this fix at below link.

https://support.microsoft.com/en-us/kb/3025036

Write if you have any query.

Resolving or fixings compiler error in VBA

Hello Friends,

Most of the times we face compiler error even we have written all the code correctly.

Cause:

When we get "Compiler Error in hidden module" error that means Your excel project is missing one or more reference libraries or its not properly registered. 

Identify:

When you get such error goto VBA Editor window and check all libraries under Tool->Library and you wil find word missing before library name which is causing an error.

Fix:

If library is missing:

For temporary fix you can select lower version of library.

 For example if your project is showing Microsoft Active Data Object 6.1 library as missing then you can select Microsoft Active Data Object 2.0 which is a lower version. 

Or you can try permanent solution: Download that library and store on users machine and register it using below command

Open command prompt and type

Regsv32 "complete path of library"

If library is available on user machine and still showing as missing:

Then first unload library using below command and then register it.

Regsv32 /u "complete path of library"

How to avoid this error:

Write your library dependant object code in late binding form

Learn excel VBA online from an industry experience developer to strengthen your knowledge go for a structured VBA course. 

How to fix automation error in VBA-Marco

If you are getting automation error in your VBA protect. Then below steps possibly fix your problem:

Cause:

Such issue can arise due to mscomctl.ocx file which is an common control library

Fix:
If your project is throwing an error on specific machine then it can be a version issue of common control library. In such situations cross check version of culprit library with one on working machine. if any discrepancy found then take library from working machine and replace with culprit machine.

Mostly you will find such library in system32 folder or you can search them in file system or you can also see location in Tools->Library menu in VBA editor window of VBA by selecting respective library as shown below:

Once you replace library then register library with below command in command prompt or Run window:

Regsv32 /u "complete path of library"

to unload file then 

Regsv32 "complete path of library"

to register file. if you get any error then try with admin access.

Questions

You can post your questions in comments section of this post and I will revert you back.

Thank you

How to use dynamic array in VBA / macro

Sorry Pravin to keeping your query waiting. You asked me a query about how to use dynamic array in VBA but in my last post I explained about basic use of array but my blog is for all the readers ;-).

So here we go, We get into such scenario where we actually doesn't know what length of array we would need in our program and here dynamic array helps you. Lets check a example program:

Ex1:

Sub MyDynArray ()

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

Redim Preserve MyArr( iArrLenghth-1)

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

End Sub

In example code is storing all numbers in column A of sheet1 to an Array variable and then searches in array for number 5 and throws message if number 5 found and ends.

In example procedure at first line I have declared dynamic array with other variables

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

while declaring dynamic array we doesn't specify its length.

Next line I get count of rows having data in column A of sheet1 into variable.

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

At next line we set length of array variable at runtime so if column A has 5 rows of data then Array's length would be 0 to 4.

Redim Preserve MyArr( iArrLenghth-1)

Here we can also use only Redim no need to use Preserve keyword.

Preserve is required only if we have data stored in array and we do not want to loose it.
If our array is having some data stored and if we do not use Preserve keyword as shown below then all stored data will wipe out.

Redim MyArr( iArrLenghth-1)

Next I have use a for loop to store value from column A of sheet1 to array variable

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

After that I have used another fir loop which I am using to loop through all elements of array variable and to find if any element is having value as 5 and if found then exit loop.

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

Here I have used inbuilt function UBound() which gives length if array. and I have used Exit For which get use to terminate For loop.

So here are some basics which will help you to manipulate  data in array and access to flexible array length.

Let me know in case of any queries.

Thanks

.

How to use array in VBA (macro)

Thanks to my friend Pravin who asked me about his array query and lead me to write this post. So basically, an array is bunch of a same kind of variables stored under one variable. You should know what is variable and their types before getting into array.

So an array can have any store number of depends on RAM size of your machine.
So enough theory just check an example below:

Ex1 :

Sub MyFirstArray ()

Dim MyArray (2) as Integer

MyArray (0) = 21
MyArray (1) = 30

Msgbox " First array value = " & MyArray (0)
Msgbox " Second array value = " & MyArray (1)

End Sub

You can see first line in MyFirstArray procedure is declaration of array means you told program about the length of array you would gonna use. Array always start with 0 by default, thus in the second line I have use.

MyArray (0) = 21

Meaning first place of my array which is having an index of 0,  will store value 21.

similarly second place of array that is last place, which is having an index of 1 will a store value 30.

This is how we can assign a value to an array.

Now how to get or access value from array so in 4th and 5th line of procedure we have show stored values in message box.

So this is about basic you should know to start with array.
So please try with other types of variables and get back to me in case of and query.

But my friend Pravin's actual query was how to use dynamic array is in my next post.......