Tag: Excel 2010

Select Non Blank cells of ActiveSheet

If you want to replace or select non-blank cells on the particular columns in active sheet, use the below codes: 

  Input: I have selected Non blank cells on the Active Sheet and replaced with 1. Result:

INDEX – MATCH Formula’s Combination

INDEX – MATCH Formula’s Combination VLookup – Searching a value in the left-most column of table array and return value based on column index number.   Combined INDEX MATCH –   formulas work as a right to left lookup, whereas VLOOKUP only works left to right. We can use INDEX MATCH to lookup a value that is …

Continue reading

VBA – Turn off Auto Filter

Turn off Auto Filter in Active sheet   If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False     Turn off Auto Filter in Table   If Sheet1.ListObjects(“Table1”).ShowAutoFilter = True Then              Sheet1.ListObjects(“Table1”).ShowAutoFilter = False      End If

“Cannot insert object” error in an ActiveX -Security Patch Issue

Affected Users: Global Problem:    You will get one of the Error in Excel   Object library invalid or contains references to object definitions that could not be found. Cannot insert object. Solution:-1   Please check your system whether you have Admin rights.  if not through following steps   Step1: Click the bottom of the right …

Continue reading

Formulas Not working when dragging down

Excel Issues/ Problem #1: Issue: Formulas Not working when I drag it down: when I drag a formula from the cell on downwards, the numbers remain the same in all the pasted cells. When I double click on the individual formulas, it updates. Solution: Go to Formulas Menu -> calculation- change it to Automatic   …

Continue reading

#2:Compare two lists instantly

Excel Intermediate:  #2:Compare two lists instantly Solution:  1. Select  range in both lists  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values > OK  Play this trick immediately to do your quick comparison instantly  Benefits: No need to spend time in writing formulas /code Instant way Alternatively you can watch my below video …

Continue reading

#1:Connect to Internet Explorer(IE) via VBA

VBA Intermediate:  #1:Connect to Internet Explorer(IE) via VBA Solution:  1. Using Late binding methodology create object of IE and make the visible property as true.  2. Then Navigate to your URL  Benefits:  Automate your day to day work Save your time Increase your productivity Alternatively you can watch my below video for better clarity  If …

Continue reading

#1:Creating Excel menus using your Sub Routine

#1:Creating Excel menus using your Sub Routine- Excel Intermediate Level Solution:  1. First make sure which Sub Routine you want to add it in a menu  2. Click > File > Options> Customize Ribbon 3. Now from the Right side Panel > Click New Tab and Name it Properly  4. From Choose Commands from drop down …

Continue reading

Finding Upper Bound and Lower Bound Range from DataSet

Excel Advanced Formula Tricks #2:  How to find the Upper Bound Range and Lower Bound Range from the given data set? Solution:            We can achieve this by using Match Function in Excel. There is no direct way to do this in Excel.  Please watch the video below to achieve this …

Continue reading

Restrict the work area few columns and rows

Excel productivity Tweaks #2:   Restrict the work area few columns and rows: Select the first column you don’t want to see  ( Ex :L Column) Now  press ctrl+shift+right arrow , now right click on the header and then press hide option. Now select the first row you don’t want to see (Ex:18 th row) …

Continue reading