Anil Nahar - Ready to Use 101 Powerful Excel VBA Code Just Copy - Paste - Run (For Functional Users)

Disclaimer Ready to Use 101 Excel Powerful Microsoft Excel VBA Code is an independent publication and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft Corporation. Trademarks Microsoft, Visual Basic, Excel and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Liability The purpose of 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. Although every effort and care has been taken to make the information as accurate as possible, the author shall not be liable for any error, harm or damage arising from using the instructions given in this book. ISBN: 9781973519478 All rights reserved.

No part of this book may be reproduced or transmitted in any form or by any means, without permission in writing from the author. Acknowledgement I would like to express my sincere gratitude to many people and Excel Learning Websites who have made their contributions in one way or another to the successful publication of this book. My special thanks go to my Parents and Gurudev (Acharya Sh Ramesh) and friend Mr. Rakesh Jain (Charvi Associates) having expert knowledge of web programming & My Accounts, HR, IT & ERP Departments Colleague contributed their ideas and even wrote some of the sample programs for this book. I would also like to appreciate the support provided by my beloved wife Manisha Jain, son Master Samkit and youngest daughter Samiksha Jain and Friends. I would also like to thank the million of visitors to my Smart Excel & Learning website at http://www.anilnahar.com l for their support and encouragement.

About the Author Anil Nahar holds a Master degree in Computer Application (MCA), a Bachelor degree in Commerce (B.Com.) and Three year completed C.A. training by ICAI ( Institute of Chartered Accountant of India). He is working on real platform with a esteem organization on Functional as well as Technical Support in Excel. He is provided many smart excel training programmes in corporate and appreciated by management. He has been involved in programming for more than 10 years. He created the popular online Smart Learning Tricks Tutorial at www.anilnahar.com in 2017 and since then the web site has attracted millions of visitors and it is one of the top searched VBA Excel websites in many search engines including Google.

Table of Contents

How to Insert VBA code to Excel Workbook

This is a brief step-by-step guideline for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks. This step-by-step guide to be able to use the code you found in this E-Book :

1. Open your workbook in Excel.
2. Press Alt+F11 to open Visual Basic Editor (VBE).

1. Click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.

1. Copy the VBA code and paste it to the right pane of the VBA editor ("Module1" window).

1. Then add the following lines to get your macro to work faster.

To the very beginning of the code, after all code lines that start with Dim (if there are no "Dim" lines, then add them right after the Sub line): Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual To the very of the code, before End Sub: Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro. After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells' contents). Save your workbook as "Excel macro-enabled workbook". The "Save as" dialog will open. The "Save as" dialog will open.

Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button. Press Alt+Q to close the Editor window and switch back to your workbook.

How to run VBA macros in Excel

When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the "Macro" dialog. Then select the wanted macro from the " Macro Name " list and click the " Run " button.

Sub add_substract_all() ' Smart code for Adding/Substract Number by Input to all selection range value ' For Substract Value Should be in Negative ' Smart Excel (anilnahar.com) Dim rng As Range Dim i As Integer i = InputBox("Enter number Adding/Substract", "Value from Adding/Substract") For Each rng In Selection If WorksheetFunction.IsNumber(rng) Then rng.Value = rng + i Else End If Next rng End Sub

Sub Alphabets_capital() ' Smart code for Serial Alphabets in Capital Letters from select cell ' Smart Excel(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

Sub AutoFitColumns() ' Smart code for Autofit all columns of Active Worksheet ' Smart Excel(anilnahar.com) Cells.Select Cells.EntireColumn.AutoFit End Sub

Sub AutoFitRows() ' Smart code for Autofit all rows of Active Worksheet ' Smart Excel(anilnahar.com) Cells.Select Cells.EntireRow.AutoFit End Sub

Sub AutoSave() ' Smart code for Auto Save and quit workbook a certain time ' Smart Excel(anilnahar.com) Application.DisplayAlerts = False ActiveWorkbook.Save Application.DisplayAlerts = True Application.Quit End Sub

Private Sub Workbook_Open() Range("D2").Value = Range("D2").Value + 1 End Sub

Sub Zero_blankcell() ' Smart code for fill zero value in blank cell in selection range ' Smart Excel(anilnahar.com) Dim rng As Range Selection.Value = Selection.Value For Each rng In Selection If rng = "" Or rng = " " Then rng.Value = "0" Else End If Next rng End Sub

Sub OpenCalculator() ' Smart code for Open Windows Calculator directly ' Smart Excel(anilnahar.com) Application.ActivateMicrosoftApp Index:=0 End Sub

Public Sub SetDataFieldsToSum() 'Smart code for Change multiple field settings in pivot table Dim xPF As PivotField Dim WorkRng As Range Set WorkRng = Application.Selection With WorkRng.PivotTable .ManualUpdate = True For Each xPF In .DataFields With xPF .Function = xlSum .NumberFormat = "#,##0" End With Next .ManualUpdate = False End With End Sub