SnatchExcel-One place- Making you as Tech Geek

Feb 07

Select Non Blank cells of ActiveSheet

By Suresh Kumar Kuppusamy in Excel & VBA

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

Sub SelectAllNonBlankCell()

       Dim rCell As Range

       Dim rFullRange As Range

       For Each rCell In ActiveSheet.UsedRange

       On Error Resume Next

                 If rCell <> "" Then

                    Set rFullRange = Union(rCell, IIf(rFullRange Is Nothing, rCell, rFullRange))

                 End If

       On Error GoTo 0

      Next rCell

      rFullRange.Select

End Sub

Sub SelectAllNonBlankCell()

Dim rCell As Range

Dim rFullRange As Range

For Each rCell In ActiveSheet.UsedRange

On Error Resume Next

If rCell <> "" Then

Set rFullRange = Union(rCell, IIf(rFullRange Is Nothing, rCell, rFullRange))

End If

On Error GoTo 0

Next rCell

rFullRange.Select

End Sub

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

Jan 09

Samsung SmartTV-YouTube App-Blackscreen

By Ragavan Rajan in Google

Quick fix for the samsung smartTV -Youtube App / NetFlix App – Black screen problem. Please watch the below video . Hope it helps. Please leave your comments below.

Oct 05

Google Pixel Phones

By Ragavan Rajan in Google Pixel

The first phone made by Google – PIXEL. This time, Google has manufactured his own hardware and released two phones namely PIXEL and PIXEL XL . The interesting thing is it comes with VR headset ( Virtual Reality). Differentiated primarily by the size and resolution of their screens — 5-inch 1080p display on the …

Sep 26

Google Allo-Competitor for Whatsapp?

By Ragavan Rajan in Google Allo

Google launched a new one this week called Allo. Google actually unveiled Allo (a messaging app) and Duo (a video calling app) at Google I/O in May, but just hours ago, Allo finally made its way to the App Store on iOS and Google Play on Android. “What’s the big deal?” you might ask. Google …

Allo, Allow, Chat, Google, Google Allo, Google Assistant, messenger, Social Networking, Whatsapp
Leave comment

Aug 30

SharePoint CSOM- Turn off the formatted XML View

By Ragavan Rajan in Intermediate

Tools> Internet options> Content >Feed settings > Un check Feed reading view Note: You have to close the browser and start it again

Jan 22

INDEX – MATCH Formula’s Combination

By Suresh Kumar Kuppusamy in Excel Advanced Formula

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 …

Sep 22

VBA – Turn off Auto Filter

By Suresh Kumar Kuppusamy in Intermediate

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

Mar 26

Excel 2013 very slow

By Ragavan Rajan in Excel Issues/Problem

Affected users: Global Problem: Excel 2013 is very slow When I copy and paste some data Excel is crashing and taking long time Excel performance is really slow if I filter any data on the cell Solution:- Task 1: Increase your virtual memory Open the control panel > on the right hand top corner >Select …

Mar 23

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

By Ragavan Rajan in Excel Issues/Problem

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 …

Dec 09

Sharon Fruit

By Ragavan Rajan in Sharon Fruit

I had the best fruit in my life. I still remember the taste of Sharon Fruit. It was delicious.. Yummy !!! Please check out my Facebook Sharon Fruit page if you are also interested !! Click here to view my FB page. Feel free to hit the like button

Restrict the work area few columns and rows

Finding Upper Bound and Lower Bound Range from DataSet

Formulas Not working when dragging down

OneNote Tips and Tricks#7

Excel Advanced Formula Tricks: Min ignore Zero for non-contiguous range