Count of Words in a Cell by UDF

Function for counting words in a cell excluding space (ready vba code just copy , paste and run)

Function COUNTWORDS(Text_String As String) As Integer
'User define function counts the number of words in a cell exclude space
'www.anilnahar.com
String_Length = Len(Text_String)
For Current_Character = 1 To String_Length
COUNTWORDS = 0
Dim String_Length As Integer
Dim Current_Character As Integer
If (Mid(Text_String, Current_Character, 1)) = " " Then
COUNTWORDS = COUNTWORDS + 1
End If
Next Current_Character
End Function

Alphabets Serial Capital And Small Letter

Sub Alphabets_capital()
‘ Smart code for Serial Alphabets in Capital Letters from select cell
‘ Smart Excel (www.anilnahar.com)
Dim i As Integer
For i = 65 To 90
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub
—————
Sub Alphabets_small()
‘ Smart code for Serial Alphabates in Small Letters from select cell
‘ Smart Excel(anilnahar.com)
Dim i As Integer
For i = 97 To 122
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub

Insert Multiple Text file in Worksheet ready vba code

Sub MulipleTextFiles()
‘SmartCode for Insert Multiple text file in worksheets
‘SmartExcel(www.anilnahar.com)

Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = “|”
xFilesToOpen = Application.GetOpenFilename(“Text Files (*.txt), *.txt”, , “Smart Excel”, , True)
If TypeName(xFilesToOpen) = “Boolean” Then
MsgBox “No files were selected”, , “Smart Excel”
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
xWb.Worksheets(I).Columns(“A:A”).TextToColumns _
Destination:=Range(“A1″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=”|”
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
With xWb
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
.Worksheets(I).Columns(“A:A”).TextToColumns _
Destination:=Range(“A1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=xDelimiter
End With
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , “SmartExcel(www.anilnahar.com)”
Resume ExitHandler
End Sub

Ready to Use 101 Powerful MS Excel VBA Code

Increase Your Excel Productivity 10x :

 

This book is to provide basic guideline for people interested in Excel VBA programming. Collection of ready code has been used on reality platform by technical and non technical users on their routine work in corporate and not corporate world. Collection of VBA Code just paste in VBA Editor and Run Macro , no need require any technical coding language. It become smarter in excel with increase desk productivity work 10X.It just publishing for helping to easy work in excel on demand of users.

 

 US Zone

 

Free Kindle Reader Edition (Amzon.Com)

 

 

 India Zone

 

Free Kindle Reader Edition (Amzon.In)

 

 

Table of Contents (101 Excel VBA Code)

 

 

How to Insert VBA code to Excel Workbook. 8

How to run VBA macros in Excel 12

Adding Or Subtract By Specific Value To All 13

Alphabets Serial Capital And Small Letter. 14

Auto Fit Columns. 15

Auto Fit Rows. 16

Auto Save And Close Workbook. 17

Automatically Invoice Number Generator. 18

Blank Cell Fill With Zero Value. 19

Calculator Open. 20

Change Multiple Field Settings In Pivot Table. 21

Chart Heading Mark. 22

Combine Duplicate Rows And Sum The Values. 23

Convert Columns And Rows Into Single Column. 24

Convert Month Name To Number. 26

Convert Negative To Positive Value. 27

Convert Number To Month Name. 28

Convert One Cell To Multiple Rows. 29

Convert Text To Column. 30

Count Number Of  Words In Selected Range. 32

Count Total Words In Worksheet. 34

Create A Monthly Calendar. 35

Create Folders Based On Cell Values By Selection Range. 41

Cube Root To All 42

Data Entry Form Of Activate Sheet. 43

Delete All Blank Worksheets. 44

Delete All Worksheets. 45

Delete Apostrophe In Text Or Number. 46

Delete Decimal Value. 47

Delete Every Other Row In Selection. 48

Delete Input Value In Range. 49

Fill Blank Cells With 0 Or Other Specific Value. 50

Find Duplicates Value From Two Columns. 51

Hide All Inactive Worksheets. 52

Highilght Specific Text. 53

Highlight Alternate Rows With Color. 54

Highlight Color Maximum Ten And Other Number. 55

Highlight Color Of  Duplicate Value. 56

Highlight Greater Than Value By Input. 57

Highlight Highest Value. 58

Highlight Lowest Value. 59

Highlight Mispelled Cell Text. 60

Highlight Name Range Values. 61

Highlight Negative Number. 62

Highlight Text With Color Based On Second Column. 63

Highlight Unique Value. 65

Image Conversion Of Selection Area. 66

Image Creation Of Chart. 67

Image Linked Of  Selction Area. 68

Import All Files Path And Summary Of Folder And Sub Folder. 69

Import Multiple Text Files. 72

Index Worksheets With Hyperlink. 75

Indexing All Worksheet Name In  Of Active Workbook. 76

Indexing Name Of Files In Windows Folder. 82

Insert  Worksheets. 84

Insert Columns. 85

Insert Header And Footer By Input Text. 86

Insert Header And Footer Current Date. 87

Insert Rows. 88

Inserting All Worksheets Names In Cells. 89

Lock Formula Cell 90

Lower Case All 91

Merge All Worksheets Of Active Workbook Into One Worksheet  92

Multiply By Specific Value To All 93

Password Breaker Workbook. 94

Password Breaker Worksheet. 95

Password Protect Without Unprotecting Worksheet. 96

Password  Protected Workbook. 98

Password Unprotected Workbook. 99

Pivot Table Update Auto. 100

Print And Print Preview To Area By Input. 101

Print Comments In Last Page. 102

Print Multiple Selection Range Only. 103

Proper Case All 104

Protect And Unprotect Worksheets. 105

Protect To Other Insert Worksheet. 106

Remove Blank Rows Of The Selected Range. 107

Remove Entire Rows Based On Cell Value. 108

Remove Leading Spaces. 109

Remove Wrap Text. 110

Rename All Sheets By Entering A Specific Name. 111

Rename Worksheets By A Specific Cell Value. 112

Repeat Cell Values X Times. 113

Save As Worksheet To Workbook. 114

Select All Bold Cells In A Range. 115

Select Entire Column Except Header. 116

Sentance Case Conversion. 117

Sort Sheets In Alphabetical 118

Sort Worksheet Tabs By Color. 119

Sorting All Worksheets By Ascending Or Descending. 121

Split A Workbook Into Multiple Workbooks And Save In The Same Folder  122

Split Cells Into Multiple Rows Based On Carriage Returns. 123

Split Data Into Multiple Worksheets Based On Column. 124

Split Data Into Multiple Worksheets By Rows Count. 126

Split Word Or Number Into Separate Cells. 127

Square Root To All 128

Status Bar Progress. 129

Swap Two Nonadjacent Cell Contents. 130

Unhide All Hidden Worksheets. 131

Unhide All Rows And Columns. 132

Unmerge Cells. 133

Upper Case All 134

Wrap Text Of Selection Range. 135