ADVANCE FILTER

 

100 use full macro

Table of Content
1. create a backup of a current workbook
2. close all workbooks at once
3. hide all but the active worksheet
4. unhide all hidden worksheets
5. delete all but the active worksheet
6. copy active worksheet into a new
workbook
7. protect all worksheets instantly
8. convert all formulas into values
9. remove spaces from selected cells
10. highlight duplicates from selection
11. hide all pivot table subtotals
12. refresh all pivot tables
13. resize all charts in a worksheet
14. highlight the active row and column
15. save selected range as a pdf
16. create a table of content
17. remove characters from a string
18. active workbook in an email
19. convert range into an image
20. insert a linked picture
21. highlight top 10 values
22. add serial numbers
23. insert multiple worksheets
24. highlight named ranges
25. highlight greater than values
26. highlight lower than values
27. protect worksheet
28. unprotect worksheet
29. convert text to upper case
30. convert text to lower case
31. insert multiple columns
32. insert multiple rows
33. auto fit columns
34. auto fit rows
35. remove text wrap
36. unmerge cells
37. change chart type
38. paste chart as an image
39. add chart title
40. reverse text
41. sort worksheets
42. add workbook to a mail attachment
43. activate r1c1 reference style
44. activate a1 reference style
45. open calculator
46. use text to speech
47. activate user form
48. insert timestamp
49. create pivot table
50. update pivot table range
51. welcome message
52. closing message
53. convert date into day
54. convert date into month
55. convert date into year
56. remove time from date
57. remove date from time
58. add header/footer date
59. add custom header
60. disable getpivotdata
61. change to uppercase
62. change to lowercase
63. change to proper case
64. change to testcase
65. remove a character from selection
66. relative to absolute reference
67. remove apostrophe from a number
68. highlight negative numbers
69. highlight specific text
70. add/remove decimals to numbers
71. multiply all the values with a number
72. add a number in all the numbers
73. calculate square root
74. calculate cube root
75. highlight cells with comments
76. highlight cells with misspelled words
77. highlight alternate rows in the selection
78. protect all the cells with formulas
79. add a-z alphabet in a range
80. count open unsaved workbooks
81. delete all blank worksheets
82. convert numbers into roman number
83. use goal seek
84. count cells with error in entire worksheet
85. count cells with a specific value in
worksheet
86. highlight all the cells in the worksheet
which are blank but have an invisible
space.
87. highlight max value in the range
88. highlight min value in the range
89. highlight unique values
90. open a workbook
91. show progress on status bar
92. disable page breaks
93. highlight difference in columns
94. highlight difference in rows
95. print comments
96. print selection
97. print narrow margin
98. print custom pages
99. remove negative numbers
100. replace blank cells with zeros



1. create a backup of a current workbook
Sub FileBackUp()
ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & _
"" & Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.name
End Sub
2. close all workbooks at once
Sub CloseAllWorkbooks()
Dim wbs As Workbook
For Each wbs In Workbooks
wbs.Close SaveChanges:=True
Next wb
End Sub
3. hide all but the active worksheet
Sub HideWorksheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub
4. unhide all hidden worksheets
Sub UnhideAllWorksheet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
5. delete all but the active worksheet
Sub DeleteWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.name <> ThisWorkbook.ActiveSheet.name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub
6. copy active worksheet into a new workbook
Sub CopyWorksheetToNewWorkbook()
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub
7. protect all worksheets instantly
Sub ProtecAllWorskeets()
Dim ws As Worksheet
Dim ps As String
ps = InputBox("Enter a Password.", vbOKCancel)
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=ps
Next ws
End Sub
8. convert all formulas into values
Sub ConvertToValues()
Dim MyRange As Range
Dim MyCell As Range
Select Case MsgBox("You Can't Undo This Action. " & "Save
Workbook First?", vbYesNoCancel, "Alert")
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set MyRange = Selection
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Next MyCell
End Sub
9. remove spaces from selected cells
Sub RemoveSpaces()
Dim myRange As Range
Dim myCell As Range
Select Case MsgBox("You Can't Undo This Action. " & "Save
Workbook First?", _
vbYesNoCancel, "Alert")
Case Is = vbYesThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set myRange = Selection
For Each myCell In myRange
If Not IsEmpty(myCell) Then
myCell = Trim(myCell)
End If
Next myCell
End Sub
10. highlight duplicates from selection
Sub HighlightDuplicateValues()
Dim myRange As Range
Dim myCell As Range
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 36
End If
Next myCell
End Sub
11. hide all pivot table subtotals
Sub HideSubtotals()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.name)
If pt Is Nothing Then
MsgBox "You must place your cursor inside of a PivotTable."
Exit Sub
End If
For Each pf In pt.PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
End Sub
12. refresh all pivot tables
Sub RefreshAllPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
13. resize all charts in a worksheet
Sub Resize_Charts()
Dim i As Integer
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Width = 300
.Height = 200
End With
Next i
End Sub
14. highlight the active row and column
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)
Dim strRange As String
strRange = Target.Cells.Address & "," & _
Target.Cells.EntireColumn.Address & "," & _
Target.Cells.EntireRow.Address
Range(strRange).Select
End Sub
Sub SaveAsPDF()
Selection.ExportAsFixedFormat Type:=xlTypePDF,
OpenAfterPublish:=True
End Sub
15. save selected range as a pdf
16. create a table of content
Sub TableofContent()
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Table of Content").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Content"
For i = 1 To Sheets.Count
With ActiveSheet
.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
ScreenTip:=Sheets(i).Name, _
TextToDisplay:=Sheets(i).Name
End With
Next i
End Sub
Sub SaveAsPDF()
Selection.ExportAsFixedFormat Type:=xlTypePDF,
OpenAfterPublish:=True
End Sub
15. save selected range as a pdf
16. create a table of content
Sub TableofContent()
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Table of Content").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Content"
For i = 1 To Sheets.Count
With ActiveSheet
.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
ScreenTip:=Sheets(i).Name, _
TextToDisplay:=Sheets(i).Name
End With
Next i
End Sub
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
End Function
17. remove characters from a string
18. active workbook in an email
Sub Send_Mail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = "Sales@FrontLinePaper.com"
.Subject = "Growth Report"
.Body = "Hello Team, Please find attached Growth Report."
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
19. convert range into an image
Sub PasteAsPicture()
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Pictures.Paste.Select
End Sub
Sub LinkedPicture()
Selection.Copy
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub
20. insert a linked picture
21. highlight top 10 values
Sub TopTen()
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).S
etFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = False
End With
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub AddSerialNumbers()
Dim i As Integer
On Error GoTo Last
i = InputBox("Enter Value", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:
Exit Sub
End Sub
22. add serial numbers
23. insert multiple worksheets
Sub InsertMultipleSheets()
Dim i As Integer
i = InputBox("Enter number of sheets to insert.", "Enter Multiple
Sheets")
Sheets.Add After:=ActiveSheet, Count:=i
End Sub
24. highlight named ranges
Sub HighlightRanges()
Dim RangeName As Name
Dim HighlightRange As Range
On Error Resume Next
For Each RangeName In ActiveWorkbook.Names
Set HighlightRange = RangeName.RefersToRange
HighlightRange.Interior.ColorIndex = 36
Next RangeName
End Sub
25. highlight greater than values
Sub HighlightGreaterThanValues()
Dim i As Integer
i = InputBox("Enter Greater Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
etFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(31, 218, 154)
End With
End Sub
26. highlight lower than values
Sub HighlightLowerThanValues()
Dim i As Integer
i = InputBox("Enter Lower Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLower, Formula1:=i
27. protect worksheet
Sub ProtectWS()
ActiveSheet.Protect "mypassword", True, True
End Sub
30. convert text to lower case
Sub ConvertLowerCase()
Dim rng As Range
For Each rng In Selection
rng = LCase(rng)
Next rng
End Sub
28. unprotect worksheet
Sub UnprotectWS()
ActiveSheet.Unprotect "mypassword"
End Sub
29. convert text to upper case
Sub ConvertUpperCase()
Dim rng As Range
For Each rng In Selection
rng = UCase(rng)
Next rng
End Sub
31. insert multiple columns
Sub InsertMultipleColumns()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireColumn.Select
On Error GoTo Last
i = InputBox("Enter number of columns to insert", "Insert
Columns")
For j = 1 To i
Selection.Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromRightorAbove
Next j
Last:
Exit Sub
End Sub
32. insert multiple rows
Sub InsertMultipleRows()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireRow.Select
On Error GoTo Last
i = InputBox("Enter number of columns to insert", "Insert Columns")
For j = 1 To i
Selection.Insert Shift:=xlToDown,
CopyOrigin:=xlFormatFromRightorAbove
Next j
Last:
Exit Sub
End Sub
33. auto fit columns
Sub AutoFitRows()
Cells.Select
Cells.EntireRow.AutoFit
End Sub
Sub AutoFitColumns()
Cells.Select
Cells.EntireColumn.AutoFit
End Sub
34. auto fit rows
35. remove text wrap
Sub RemoveWrapText()
Cells.Select Selection.WrapText = False
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
End Sub
36. unmerge cells
Sub UnmergeCells()
Selection.UnMerge
End Sub
37. change chart type
Sub ConvertChartToPicture()
ActiveChart.ChartArea.Copy
ActiveSheet.Range("A1").Select
ActiveSheet.Pictures.Paste.Select
End Sub
Sub ChangeChartType()
ActiveChart.ChartType = xlColumnClustered
End Sub
38. paste chart as an image
39. add chart title
Sub AddChartTitle()
Dim i As Variant
i = InputBox("Please enter your chart title", "Chart Title")
On Error GoTo Last
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = i
Last:
Exit Sub
End Sub
40. reverse text
Public Function rvrse(ByVal cell As Range) As String
rvrse = VBA.strReverse(cell.Value)
End Function
41. sort worksheets
Sub SortWorksheets()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort
Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub
42. add workbook to a mail attachment
Sub OpenWorkbookAsAttachment()
Application.Dialogs(xlDialogSendMail).Show
End Sub
43. activate r1c1 reference style
Sub ActivateR1C1()
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlR1C1
End If
End Sub
44. Activate A1 Reference Style
Sub ActivateA1()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlA1
End If
End Sub
45. Open Calculator
Sub OpenCalculator()
Application.ActivateMicrosoftApp Index:=0
End Sub
46. use text to speech
Sub Speak()
Selection.Speak
End Sub
47. activate user form
Sub DataForm()
ActiveSheet.ShowDataForm
End Sub
48. insert timestamp
Sub TimeStamp()
Dim i As Integer
For i = 1 To 24
ActiveCell.FormulaR1C1 = i & ":00"
ActiveCell.NumberFormat = "[$-409]h:mm AM/PM;@"
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Select
Next i
End Sub
49. create pivot table
50. Update Pivot Table Range
51. welcome message
Sub auto_open()
MsgBox "Welcome To ExcelChamps & Thanks for downloading
this file."
End Sub
52. closing message
Sub auto_close()
MsgBox "Bye Bye! Don't forget to check other cool stuff on
excelchamps.com"
End Sub
53. convert date into day
Sub date2day()
Dim tempCell As Range
Selection.Value = Selection.Value
For Each tempCell In Selection
If IsDate(tempCell) = True Then
With tempCell
.Value = Day(tempCell)
.NumberFormat = "0"
End With
End If
Next tempCell
End Sub
54. convert date into year
Sub date2year()
Dim tempCell As Range
Selection.Value = Selection.Value
For Each tempCell In Selection
If IsDate(tempCell) = True ThenWith tempCell
.Value = Year(tempCell)
.NumberFormat = "0"
End With
End If
Next tempCell
End Sub
55. remove time from date
Sub RemoveTime()
Dim Rng As Range
For Each Rng In Selection
If IsDate(Rng) = True Then
Rng.Value = VBA.Int(Rng.Value)
End If
Next
Selection.NumberFormat = "dd-mmm-yy"
End Sub
56. remove date from date & time
Sub removeDate()
Dim Rng As Range
For Each Rng In Selection
If IsDate(Rng) = True Then
Rng.Value = Rng.Value - VBA.Fix(Rng.Value)
End If
Next
Selection.NumberFormat = "hh:mm:ss am/pm"
End Sub
57. add header/footer date
58. custom header/footer
Sub dateInHeader()
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
ActiveWindow.View = xlNormalView
End Sub
Sub customHeader()
Dim myText As String
myText = InputBox("Enter your text here", "Enter Text")
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = myText
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
End Sub
59. disable/enable get pivot data
60. convert to upper case
Sub activateGetPivotData()
Application.GenerateGetPivotData = True
End Sub
Sub deactivateGetPivotData()
Application.GenerateGetPivotData = False
End Sub
Sub convertUpperCase()
Dim Rng As Range
For Each Rng In Selection
If Application.WorksheetFunction.IsText(Rng) Then
Rng.Value = UCase(Rng)
End If
Next
End Sub
61. convert to lower case
62. convert to upper case
Sub convertLowerCase()
Dim Rng As Range
For Each Rng In Selection
If Application.WorksheetFunction.IsText(Rng) Then
Rng.Value = LCase(Rng)
End If
Next
End Sub
Sub convertProperCase()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsText(Rng) Then
Rng.Value = WorksheetFunction.Proper(Rng.Value)
End If
Next
End Sub
63. convert to upper case
Sub convertTextCase()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsText(Rng) Then
Rng.Value = UCase(Left(Rng, 1)) & LCase(Right(Rng, Len(Rng) - 1))
End If
64. remove a character from selection
Next rng
End Sub
66. remove apostrophe from a number
Sub removeChar()
Dim Rng As Range
Dim rc As String
rc = InputBox("Character(s) to Replace", "Enter Value")
For Each Rng In Selection
Selection.Replace What:=rc, Replacement:=""
Next
End Sub
65. relative to absolute reference
Sub relToAbs()
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next c
End Sub
Sub removeApostrophes()
Selection.Value = Selection.Value
End Sub
67. highlight negative numbers
Next rng
End Sub
Sub highlightNegativeNumbers()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsNumber(Rng) Then
If Rng.Value < 0 Then
Rng.Font.Color = -16776961
End If
End If
Next
End Sub
68. highlight specific text
Sub highlightValue()
Dim myStr As String
Dim myRg As Range
Dim myTxt As String
Dim myCell As Range
Dim myChar As String
Dim I As Long
Dim J As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
myTxt = ActiveWindow.RangeSelection.AddressLocal
Else
myTxt = ActiveSheet.UsedRange.AddressLocal
End If
LInput:
Set myRg = Application.InputBox("please select the data
range:", "Selection Required", myTxt, , , , , 8)
If myRg Is Nothing Then Exit Sub
If myRg.Areas.Count > 1 Then
MsgBox "not support multiple columns"
GoTo LInput
End If
If myRg.Columns.Count <> 2 Then
MsgBox "the selected range can only contain two columns "
GoTo LInput
End If
For I = 0 To myRg.Rows.Count - 1
myStr = myRg.Range("B1").Offset(I, 0).Value
With myRg.Range("A1").Offset(I, 0)
.Font.ColorIndex = 1
For J = 1 To Len(.Text)
If Mid(.Text, J, Len(myStr)) = myStr Then .Characters(J,
Len(myStr)).Font.ColorIndex = 3
Next
End With
Next I
End Sub
Sub removeDecimals()
Dim lnumber As Double
Dim lResult As Long
Dim rng As Range
For Each rng In Selection
rng.Value = Int(rng)
rng.NumberFormat = "0"
Next rng
End Sub
69. remove decimals to numbers
70. multiply all the values with a number
Sub multiplyWithNumber()
Dim rng As Range
Dim c As Integer
c = InputBox("Enter number to multiple", "Input Required")
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
rng.Value = rng * c
Else
End If
Next rng
End Sub
71. add a number in all the numbers
Sub addNumber()
Dim rng As Range
Dim i As Integer
i = InputBox("Enter number to multiple", "Input Required")
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
rng.Value = rng + i
Else
End If
Next rng
End Sub
72. calculate square root
Sub getSquareRoot()
Dim rng As Range
Dim i As Integer
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
rng.Value = Sqr(rng)
Else
End If
Next rng
End Sub
73. calculate cube root
Sub getCubeRoot()
Dim rng As Range
Dim i As Integer
For Each rng In Selection
If
74. highlight cells with comments
Sub highlightCommentCells()
Selection.SpecialCells(xlCellTypeComments).Select
Selection.Style = "Note"
End Sub
76. highlight alternate rows in the selection
Sub highlightAlternateRows()
Dim rng As Range
For Each rng In Selection.Rows
If rng.Row Mod 2 = 1 Then
rng.Style = "20% - Accent1"
rng.Value = rng ^ (1 / 3)
Else
End If
Next rng
End Sub
75. highlight cells with misspelled words
Sub HighlightMisspelledCells()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=rng.Text) Then
rng.Style = "Bad"
End If
Next rng
End Sub
77. protect all the cells with formulas
Sub lockCellsWithFormulas()
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect AllowDeletingRows:=True
End With
End Sub
78. add a-z alphabets in a range
Next rng
End Sub
Sub addcAlphabets()
Dim i As Integer
For i = 65 To 90
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub
Sub addsAlphabets()
Dim i As Integer
For i = 97 To 122
ActiveCell.Value = Chr(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub
79. count open unsaved workbooks
Sub VisibleWorkbooks()
Dim book As Workbook
Dim i As Integer
For Each book In Workbooks
If book.Saved = False Then
i = i + 1
End If
Next book
MsgBox i
End Sub
80. delete all blank worksheets
Sub deleteBlankWorksheets()
Dim Ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Ws In Application.Worksheets
If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0
Then
Ws.Delete
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
81. convert roman numbers into arabic number
Sub convertToNumbers()
Dim rng As Range
Selection.Value = Selection.Value
For Each rng In Selection
If Not WorksheetFunction.IsNonText(rng) Then
rng.Value = WorksheetFunction.Arabic(rng)
End If
Next rng
End Sub
82. use goal seek
Sub GoalSeekVBA()
Dim Target As Long
On Error GoTo Errorhandler
Target = InputBox("Enter the required value", "Enter Value")
Worksheets("Goal_Seek").Activate
With ActiveSheet
.Range("C7").GoalSeek _
Goal:=Target, _
ChangingCell:=Range("C2")
End With
Exit Sub
Errorhandler:
MsgBox ("Sorry, value is not valid.")
End Sub
83. unhide all rows and columns
Sub UnhideRowsColumns()
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
End Sub
84. save each worksheet as a single pdf
Sub SaveWorkshetAsPDF()
Dim ws As Worksheet
For Each ws In Worksheets
ws.ExportAsFixedFormat xlTypePDF, “ENTER-FOLDER-NAMEHERE"
& ws.Name & ".pdf"
Next ws
End Sub
85. count/highlight cells with error in entire
worksheet
Sub highlightErrors()
Dim rng As Range
Dim i As Integer
For Each rng In ActiveSheet.UsedRange
If WorksheetFunction.IsError(rng) Then
i = i + 1
rng.Style = "bad"
End If
Next rng
MsgBox "There are total " & i & " error(s) in this worksheet."
End Sub
86. count/highlight cells with error in entire
worksheet
Sub highlightSpecificValues()
Dim rng As Range
Dim i As Integer
Dim c As Variant
c = InputBox("Enter Value To Highlight")
For Each rng In ActiveSheet.UsedRange
If rng = c Then
rng.Style = "Note"
i = i + 1
End If
Next rng
MsgBox "There are total " & i &" "& c & " in this worksheet."
End Sub
87. highlight all the cells in the worksheet which
are blank but have an invisible space
Sub highlightMaxValue()
Dim rng As Range
For Each rng In Selection
If rng = WorksheetFunction.Max(Selection) Then
rng.Style = "Good"
End If
Next rng
End Sub
Sub blankWithSpace()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If rng.Value = " " Then
rng.Style = "Note"
End If
Next rng
End Sub
88. highlight max value in the range
89. highlight min value in the range
Sub highlightMaxValue()
Dim rng As Range
For Each rng In Selection
If rng = WorksheetFunction.Min(Selection) Then
rng.Style = "Good"
End If
Next rng
End Sub
Sub highlightUniqueValues()
Dim rng As Range
Set rng = Selection
rng.FormatConditions.Delete
Dim uv As UniqueValues
Set uv = rng.FormatConditions.AddUniqueValues
uv.DupeUnique = xlUnique
uv.Interior.Color = vbGreen
End Sub
90. highlight unique values
91. show progress on status bar
Sub progressStatusBar()
Application.StatusBar = "Start Printing the Numbers"
For icntr = 1 To 5000
Cells(icntr, 1) = icntr
Application.StatusBar = " Please wait while printing the numbers "
& Round((icntr / 5000 * 100), 0) & "%"
Next
Application.StatusBar = ""
End Sub
Sub DisablePageBreaks()
Dim wb As Workbook
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wb In Application.Workbooks
For Each Sht In wb.Worksheets
Sht.DisplayPageBreaks = False
Next Sht
Next wb
Application.ScreenUpdating = True
End Sub
92. disable page breaks
93. highlight difference in columns
Sub columnDifference()
Range("H7:H8,I7:I8").Select
Selection.ColumnDifferences(ActiveCell).Select
Selection.Style = "Bad"
End Sub
94. highlight difference in rows
Sub rowDifference()
Range("H7:H8,I7:I8").Select
Selection.RowDifferences(ActiveCell).Select
Selection.Style = "Bad"
End Sub
Sub printComments()
With ActiveSheet.PageSetup
.printComments = xlPrintSheetEnd
End With
End Sub
95. print comments
96. print with narrow margin
Sub printNarrowMargin()
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True, IgnorePrintAreas:=False
End Sub
97. print selection
Sub printSelection()
Selection.PrintOut Copies:=1, Collate:=True
End Sub
98. Print custom pages
Sub printCustomSelection()
Dim startpage As Integer
Dim endpage As Integer
startpage = InputBox("Please Enter Start Page number.", "Enter
Value")
If Not WorksheetFunction.IsNumber(startpage) Then
MsgBox "Invalid Start Page number. Please try again.", "Error"
Exit Sub
End If
endpage = InputBox("Please Enter End Page number.", "Enter
Value")
If Not WorksheetFunction.IsNumber(endpage) Then
MsgBox "Invalid End Page number. Please try again.", "Error"
Exit Sub
End If
Selection.PrintOut From:=startpage, To:=endpage, Copies:=1,
Collate:=True
End Sub
99. remove negative numbers
Sub removeNegativeSign()
Dim rng As Range
Selection.Value = Selection.Value
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
rng.Value = Abs(rng)
End If
Next rng
End Sub
100. replace blank cells with zeros
Sub replaceBlankWithZero()
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
Thank You
for reading this e-book
not for resale

all function

Text Functions

1. LEFT Function: It returns specified (by you) characters from the left side of a text string. You can extract characters up to the total length of the text string.

2. RIGHT Function: It returns specified (by you) characters from the right side of a text string. You can extract characters up to the total length of the text string.

3. MID Function: It returns specific characters from a specific position in the text string. You can extract text up to the total length of the text string.

4. LOWER Function: It converts a normal text into a lower case text where you have all the letters in small letters and numbers will be unchanged.

5. UPPER Function: It converts a normal text into an upper case text where you have all the letters in capital letters and numbers will be unchanged.

6. PROPER Function: It converts a normal text into a proper case text where you have the first letter of a word in capital letter and rests all in small and numbers will be unchanged.

7. REPT Function: It repeats the text which you specify the number of times. You just need to specify the text and the repetition number. Its biggest benefit is for creating the in-cell chart.

8. LEN Function: It returns the number of total characters a text string has. You can input a text string directly into the function or simply refer to a cell.

9. FIND Function: It helps to find the starting position of a text string (Case Sensitive) from another text string. You just need to specify the text to find and a text from which you want to find.

10. SEARCH Function: It helps to find the starting position of a text string (Non-Case Sensitive) from another text string. You just need to specify the text to find and a text from which you want to find.

Date Functions

11. DATE Function: It creates a valid date according to Excel's date format. You need to provide day, month and year to create a date.

12. DATEDIF Function: It returns the difference between the start date and end date. It has six different ways to show that difference.

13. DATEVALUE Function: It converts a text date into a valid date according to Excel's date format. You can refer to a cell or you can also enter that text directly into the function.

14. EDATE Function: It gives you a date which is a number of months before or after a specified date. In simple words, you'll get the same date in future or past month like the specific date.

15. DAY Function: It returns the day of the month ranging from 1-31 from the date you specified.

16. DAYS Function: It returns the difference between two dates using four different methods which you can specify.

17. TODAY Function: It gives you current date/today's date as per your system settings. This is a volatile function which changes its values when you recalculate your worksheet.

18. MONTH Function: It returns the month number ranging from 1-12 from the date you specified.

19. EOMONTH Function: It gives you the last date of a month which is a number of months before or after a specified date. In simple words, you'll get the last date of a future month or past month.

20. YEAR Function: It returns the year number from the date you have specified (It should be a valid date).

21. WEEKDAY Function: It returns the day number from the date you have specified ranging from 1-7.

22. WEEKNUM Function: It returns the week number of a specific date. For example, the week containing Jan 1 is the first week of the year and is numbered week 1.

23. NETWORKDAYS Function: It returns the number of working days between the start date and end date which exclude weekends and any dates identified in holidays.

24. NETWORKDAYS.INTL Function: It returns the number of workdays between the start date and end date which exclude weekends and any dates identified and specified holidays.

Time Functions

25. TIME Function: If create a valid time according to Excel's time format. You need to provide hour, minutes, and seconds.

26. TIMEVALUE Function: It converts a text time value into a valid time value according to Excel's time format. You can refer to a cell or you can also enter that text directly into the function.

27. HOUR Function: It returns the hour value (ignores minutes and seconds) from a time value ranging from 0-23 hours.

28. MINUTE Function: It returns the minute value (ignores hour and seconds) from a time value ranging from 0-59 minutes.

29. SECOND Function: It returns the seconds' value (ignores hours and minutes) from a time value ranging from 0-59 seconds.

30. NOW Function: It gives you current date and time as per your system settings. This is a volatile function which changes its values when you recalculate your worksheet.

Logical Functions

31. IF Function: You can provide a condition to check and it returns a specific value if that condition is TRUE and another value if that condition is FALSE.

32. IFERROR Function: It evaluates a value for an error and returns a specific value if an error occurs.

33. IFNA Function: It evaluates a value for #N/A error and returns a specific value if an error occurs.

34. OR Function: Test two or more conditions differently and return TRUE if any of those conditions is TRUE and FALSE if all those conditions are FALSE.

35. AND Function: Test two or more conditions jointly and return TRUE if all of those conditions is TRUE and FALSE if any of those conditions is FALSE.

36. NOT Function: It converts a TRUE into FALSE and FALSE into TRUE.

37. FALSE Function: It returns the logical value TRUE in the cell where you insert it.

38. TRUE Function: It returns the logical value FALSE in the cell where you insert it.

Maths Functions

39. SUM Function: It adds values. It can add individual values, cell references or ranges or a mix of all three.

40. SUMIF Function: It returns the sum of the numeric values of which meet a condition. You need to specify a condition and the range to check that condition.

41. SUMIFS Function: It returns the sum of the numeric values of which meet multiple conditions. You need to specify conditions and range to check those conditions.

42. SUMPRODUCT Function: It multiplies values from corresponding cells in the given arrays, and returns the sum of those products. It can take an array without using Ctrl + Shift + Enter.

43. ABS Function: It converts a number into an absolute number. The absolute value of a number is the number without its sign.

44. EVEN Function: It returns a number by rounding it to the nearest even number.

45. INT Function: It returns a number by rounding it to the nearest to the nearest integer.

46. MOD Function: It returns remainder after dividing two numbers, not the result of the division of two numbers.

47. MROUND Function: It rounds a number to the nearest multiple of a number without considering that number is greater or lower than the original number.

48. TRUNC Function: It truncates a number to an integer by removing the fractional part of the number. In simple word, it returns on integer part from a value.

49. RAND Function: It returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

Statistical Functions

50. COUNT Function: It counts the number of cells which contain numbers, and counts the numbers which are specified in the arguments.

51. COUNTA Function: It counts all the cells which are not empty. It doesn't matter which type of value is there in a cell, it counts all the cell with values in it.

52. COUNTBLANK Function: It counts all the cells which are empty/blank, but if a cell has a blank space it will not count it.

53. COUNTIF Function: It counts the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.

54. COUNTIFS Function: It counts the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.

55. AVERAGE Function: It returns the average (arithmetic mean) of the arguments. You can refer to a range of cells or insert numbers inside the function.

56. AVERAGEA Function: It returns the average (arithmetic mean) of a group of numbers and text. You can refer to a range of cells or insert numbers inside the function.

57. AVERAGEIF Function: It averages the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.

58. AVERAGEIFS Function: It averages the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.

59. MAX Function: It returns the largest value in a set of values. You can refer to a range or insert values directly into the function as well.

60. MIN Function: It returns the smallest value in a set of values. You can refer to a range or insert values directly into the function as well.

Lookup Functions

61. ADDRESS Function: It returns the address of a cell in a worksheet, given specified row and column numbers.

62. AREAS Function: It returns the number of areas in a given reference. An area can be a range of contiguous cells or a single cell.

63. CHOOSE Function: It returns a value from a list based on position given.

64. COLUMN Function: It returns the column number of the given reference.

65. COLUMNS Function: It returns the number of columns included in the given reference.

66. FORMULATEXT Function: It returns a formula as a string from the referred cell.

67. HLOOKUP Function: It searches for a value in the top row of a table or an array of values, and returns a value in the same column from a row you specify.

68. HYPERLINK Function: It creates a hyperlink that opens a document stored on a network server, an intranet, or the Internet.

69.INDEX Function: It returns a value from a column or a row based on the position of that value in the column or row.

70. INDIRECT Function: It returns the reference specified by a text string. You need to mention the text and reference style.

71. LOOKUP Function: It helps to lookup for a value from a single column or row.

72. MATCH Function: It returns a number representing the position of a cell in an array. In simple words, you get the position of a cell in from a column or row.

73. OFFSET Function: It creates a reference offset from given starting cell using height and width.

74. ROW Function: It returns the row number of the given reference.

75. ROWS Function: It returns the number of rows included in the given reference.

76. TRANSPOSE Function: It switches row into column and column into rows using Ctrl + Shift + Enter.

77. VLOOKUP Function: It looks up for a value in a column and returns the value from the right of the value that you have found.

Financial Functions

78. FV Function: It calculates the future value of an investment which is based on periodic and constant payments and on a constant rate of interest.

79. PMT Function: It calculates loan payment based on fixed monthly payments and constant rate of interest.

80. PV Function: It helps you to determine that an investment is profitable or not. For using PV function, you need a constant interest rate, constant periodic payments.

Information Functions

81. CELL Function: It returns some specific information about a cell. You need to refer to a cell and type of information you need.

82. ERROR.TYPE Function: It returns a number if the referred cell has an error. For each type of error, there is a different number it returns.

83. INFO Function: It returns information about the current operating environment. You can select the type of information you need from the function.

84. ISBLANK Function: It verifies a cell and return TRUE if that cell is blank (no value) and FALSE if that cell is not blank.

85. ISERR Function: It verifies a cell and return TRUE if that cell has an error other than #N/A and FALSE if that cell has any other value.

86. ISERROR Function: It verifies a cell and return TRUE if that cell has an error, otherwise it returns a FALSE.

87. ISEVEN Function: It verifies a cell and return TRUE if that cell has an ever, otherwise it returns a FALSE.

88. ISFORMULA Function: It verifies a cell and return TRUE if that cell has a formula, otherwise it returns a FALSE.

89. ISLOGICAL Function: It verifies a cell and return TRUE if that cell has a logical value, otherwise it returns a FALSE.

90. ISNA Function: It verifies a cell and returns TRUE if that cell has and #N/A error, otherwise it returns a FALSE.

91. ISNONTEXT Function: It verifies a cell and return TRUE if that cell has a non-text value, otherwise it returns a FALSE.

92. ISNUMBER Function: It verifies a cell and return TRUE if that cell has a numeric value, otherwise it returns a FALSE.

93. ISODD Function: It verifies a cell and return TRUE if that cell has an odd number, otherwise it returns a FALSE.

94. ISREF Function: It verifies a cell and return TRUE if that cell has a reference, otherwise it returns a FALSE.

95. ISTEXT Function: It verifies a cell and return TRUE if that cell has a text value, otherwise it returns a FALSE.

96. N Function: It converts a logical value into a number. 1 for TRUE and 0 for FALSE.

85. ISERR Function: It verifies a cell and return TRUE if that cell has an error other than #N/A and FALSE if that cell has any other value.

97. NA Function: It returns an #N/A in the cell where you enter it.

98. SHEET Function: It returns the worksheet number of the reference.

99. SHEETS Function: It returns the number of sheets in a reference.

100. TYPE Function: It returns a number representing a value type. When you refer a cell in this function, it verifies the value and returns a number to present it.