Macro

	Commands	Description
	ActiveCell.CurrentRegion.Select	Select a range or block of data (Cells). Keep columns to the left and right clear of data so that they are not apart of the block.
	Selection.End(xlDown).Select	End down
	ActiveCell.Offset(1, 0).Select	offset (Row, Col)
	ActiveSheet.DisplayPageBreaks = False	Removes dotted print lines
	Application.DisplayAlerts = False	Turn alert alerts On (=True) or Off (=False) example
	Application.ScreenUpdating = False	This is used so that you do not see the macro run. It makes the macro go faster. Note if you use this the cursor will not follow you to the new cell.
	Application.CutCopyMode = False	This is like pressing “Esc” to get rid of the rubber band box. Use this after you have pasted your data.
	ActiveWorkbook.Save ActiveWorkbook.Close Savechanges:=True	This will save the workbook This will close and save the workbook
	ActiveCell.EntireColumn.AutoFit	Adjust current column width
	ActiveCell.EntireColumn.Copy	copy active column
	Sheets("Sheet3").Select	Go to sheet 3
	[a1].Select	Go to a Cell location - If using screen updating the cursor will not follow to active cell
	Range("A1:D20").Select	Select a Range of cells
	Selection.Copy	Copy Active Selection
	ActiveSheet.Paste	Paste data to ActiveCell
	vbCrLf replace with VBNEWLINE	This will add a Carrage Return in VBA used to start a new line of data MsgBox = "First Line & vbCrLf & Second Line"
	Turn on and off the caluculation function	Application.Calculation = xlManual Application.Calculation = xlAutomatic
	Minus Minus in front of a formula. =IsNumber( ) ' would result in either True or False =--IsNumber( ) ' would result in either 0 or 1	=ISNUMBER(IFERROR(SEARCH($B$3,E3,1),"")) Would result in either True or False =--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),"")) Would result in either 0 or 1
	How to add a Key Stroke to activate a macro.	Click Developer tab / click on Macros / click Options

w3School web site

BAR Codes using excel

Get it here

Substitute Split Numbers and Letters in a Serial number

In this example the serial number LT100000001 is located in cell A1
Cell B1 will contain the following formula:
[B1].Formula = "=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,""0"",""""),""1"",),""2"",),""3"",),""4"",),""5"",),""6"",),""7"",),""8"",),""9"",))"
Cell C1 will contain the following formula:
[C1].Formula = "=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,""A"",""""),""E"",),""Q"",),""P"",),""S"",),""F"",),""J"",),""L"",),""N"",),""T"",))"
Note that the serial number is text so we add VALUE to the Statement. The word SUBSTITUTE is used for each letter to substitute starting in (A1,""A"","""") then all other substitutes ,""E"",)

Find_and_Goto_and_Find_Again

Sub FindNext()
Application.ScreenUpdating = False

Dim rng As Range
Set rng = Range("D:D")
Dim I, U, Y As Variant

U = Application.WorksheetFunction.CountIf(rng, "Dog")
For I = 1 To U
Set rng = Cells.Find(What:="Dog", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

If Not rng Is Nothing Then
rng.Select
U = ActiveCell.Row

Y = Cells(U, 6)
' MsgBox Y ' this just confirms the value of the offset and is not needed
'Else
' MsgBox "not found"
End If
Next I
End Sub

Move data from one workbook to another workbook

Sub DB()
Dim A, B, C, D, E, F, G, H, i, J, K, L, LP, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z As String
Dim dt As Date
dt = Date
Dim DB1 As Workbook
Dim DB As Workbook
Set DB = Workbooks.Open("L:\AMC_TestResults\OpticTesting\OpticTestingR8.1.xlsm")

A = Cells(2, 1) 'invoice
B = Cells(1, 3) 'StockID
Dim RNG As Range
Set RNG = Range("B:B")
Z = Application.WorksheetFunction.CountIf(RNG, "Xcvr") + 7
C = Range(Cells(8, 5), Cells(Z, 6)).Copy 'SN and PN
Cells(8, 1).Select

Do Until (ActiveCell = "Physical")
ActiveCell.End(xlDown).Select
Loop
D = ActiveCell.Offset(23, 11)
E = ActiveCell.Offset(24, 11)
F = ActiveCell.Offset(27, 11)
G = ActiveCell.Offset(28, 11)
H = ActiveCell.Offset(37, 8)
i = ActiveCell.Offset(38, 8)
J = ActiveCell.Offset(52, 8)
K = ActiveCell.Offset(53, 8)
L = ActiveCell.Offset(67, 8)
M = ActiveCell.Offset(68, 8)
N = ActiveCell.Offset(82, 8)
O = ActiveCell.Offset(83, 8)

Set DB1 = Workbooks.Open("L:\AMC_TestResults\OpticTesting\TestingDB.xlsm")

DB1.Sheets("Home").Range("A1").End(xlDown).Offset(1) = dt
DB1.Sheets("Home").Range("B1").End(xlDown).Offset(1) = A
DB1.Sheets("Home").Range("C1").End(xlDown).Offset(1) = B
DB1.Sheets("Home").Range("D1").End(xlDown).Offset(1).PasteSpecial
DB1.Sheets("Home").Range("F1").End(xlDown).Offset(1) = D
DB1.Sheets("Home").Range("G1").End(xlDown).Offset(1) = E
DB1.Sheets("Home").Range("H1").End(xlDown).Offset(1) = F
DB1.Sheets("Home").Range("I1").End(xlDown).Offset(1) = G
DB1.Sheets("Home").Range("J1").End(xlDown).Offset(1) = H
DB1.Sheets("Home").Range("K1").End(xlDown).Offset(1) = i
DB1.Sheets("Home").Range("L1").End(xlDown).Offset(1) = J
DB1.Sheets("Home").Range("M1").End(xlDown).Offset(1) = K
DB1.Sheets("Home").Range("N1").End(xlDown).Offset(1) = L
DB1.Sheets("Home").Range("O1").End(xlDown).Offset(1) = M
DB1.Sheets("Home").Range("P1").End(xlDown).Offset(1) = N
DB1.Sheets("Home").Range("Q1").End(xlDown).Offset(1) = O
Cells(2, 4).End(xlDown).Offset(0, -3).Select
If ActiveCell <> "" Then
GoTo line1
End If
Cells(2, 4).End(xlDown).Offset(0, -3).Select
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 3).Select
For T = 1 To 12
ActiveCell.End(xlUp).Offset(1) = "."
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 1).Select
Next T
Do Until (ActiveCell = "")
Cells(3, 6).Select
If ActiveCell <> "." Then
ActiveCell.Interior.ColorIndex = 4
ActiveCell.Offset(1).Select
End If
Loop [A1].Select line1: End Sub

Find Next Tx

Find Cell by color

Change a range of cells Case to Upper, Lower or Proper

Find "+" in column "C" and shift data to the left

This example uses the Left function to return a specified number of characters from the left side of a string.

Open Windows Explorer to specific folder

Keyboard shortcuts

Link to Keyboard Shortcuts

Link to Frequently used shortcuts

Uppercase a range with VBA

Print Page to PDF

'Need to Save TO pdf (Example 2)

Convert text to number

Find a cell by its color

Message Box Timer

Match for Row Number from the bottom up

=LOOKUP(2,1/($A$1:$C$100=I1),$C$1:$C$100)
=LOOKUP(2,1/(Range = Criteria),Range gets the answer from)
Looking up the last row number where cell E1 = Edith

	A	B	C	D	E
1	Dan		1		Edith
2	Edith		2
3	Dan		3
4	Edith		4

Protected Sheet will not protect

The solution is hidden on the "Review" tab in the "Changes" ribbon. There is a button called "Allow Users to Edit Ranges". You need to protect the sheet from this window.

Setting up a dynamic range using a table

Dim rng as Range
Set rng = wsS.Range("Table2[#Retail]")

How To Automate a Table in Word Using Excel

Open the Excel spreadsheet containing the data you want to display in your Word document.

Select the data you want to appear in the Word document and press "Ctrl-C" to copy it.

Launch Microsoft Word and open the document in which you wish to display the data.

Place the cursor in the area of the Word document where you want the Excel data to be displayed and right-click. Choose either "Link & Keep Source Formatting" or "Link & Use Destination Styles" depending on whether you want to use the formatting and style options from the original Excel file or the Word document respectively.

Save your documents. From now on, when you update the Excel file, the table in Word will also be updated. Be aware, however, that you will need to repeat the previous steps if you change the location or name of the Excel file.

Copy from Excel to Word

Sub export_excel_to_word()

Dim objWord As New Word.Application
'Copy the range Which you want to paste in a New Word Document
Range("A1:B10").Copy

With objWord
.Documents.Add "Y:\Edith\OCSkinINTHEBUFF_Receipt.docx"
.Selection.Paste
.Visible = True
End With

End Sub

Reset Dropdown lists

Worksheets("Records").DropDowns("Drop Down 1") = 1

Reset Combo Box

ComboBox1.ListIndex = 0
or change the target cell to 0 ... [A1] = 0

Password Protect a Button

=REPT(0,8-len(A1))&A1 ...Put Zero's 0 in front of numbers.

While loop

Here is more information on loops

Another example:

Send email from excel

Click here

StockID Searchable Table

Stock ID XLSM

Searchable Table html

Scroll

Sub scrll()
Dim x As Integer
x = [A1]
ActiveWindow.ScrollRow = x
ActiveWindow.ScrollColumn = x
End Sub

Copy and paste End(xldown)

'Write formula for E

Copy and Paste a range

Sub copy1()
'This copies To "A" From "C"
Dim LRow As Integer
Dim RNG As Range
Set RNG = Range("C1:C50000")
LRow = Application.WorksheetFunction.CountA(RNG)
Range("A1:A" & LRow).Value = Range("C1:C" & LRow).Value

End Sub

copy2()
'This copies To "A" From "C"

How to stop a running macro

BarCodes for Excel

Download and install

After Enter Move to cell (Up, Down, Left, Right

Append & Remove data based on cell A1

	A	B	C
1	PartNumber-8pack	Remove -8pack	Append -8pack to B2
2	Formula	=Left(A1,FIND("-",A1)-1)	=B2&"-8pack"
3	Result	PartNumber	PartNumber-8pack

Confirm the active cell is in column "A:A"

Intersect in a specific Range

This is controlled using the "SelectionChange" Event on the sheet not in a module.
There is an "OFF" and "ON" so that players names can be chanaged. Off & On turn off and on the event handler.
Winner is aConditional Format

Convert existing text that looks like a number to a number

'This will place the String in cell B1 to C1 as a number.

Convert the String "ABCD1234" in Cell A1 to only the numbers "1234" in B1

' This writes the formula =Right(A1,x) where x is a input variable
' Also note that the output is a number and not a String like in the example above.

VLookup in VBA

Example 2 Vlookup:

Using Match to find the row number of an item

r = Application.WorksheetFunction.Match([Z1], rng, 0)

Change UPPPERCASE / lower case / ProperCase

Sub Uppercase()
Dim rng As Range
Dim cell As Variant
'Set rng = selection (Use this line to select a range)
Set rng = Range("K17:K26") 'Use this line for a static range
For Each cell In rng
cell.Value = UCase(cell)
Next
End Sub

Sub lowercase()
Dim rng As Range
Dim cell As Variant
'Set rng = selection (Use this line to select a range)
Set rng = Range("K17:K26") 'Use this line for a static range
For Each cell In rng
cell.Value = lcase(cell)
Next
End Sub

Sub ProperCase()
Dim rng As Range
Dim cell As Variant
'Set rng = selection (Use this line to select a range)
Set rng = Range("K17:K26") 'Use this line for a static range
For Each cell In rng
cell.Value = Application.WorksheetFunction.Proper(cell)
Next
End Sub

Force Upper Lower and Proper case when using a Inputbox.

Delete the active row using vba

Rows(ActiveCell.Row).EntireRow.Delete

Place a formula in a cell

'In this example the Formula is entered in Cell C1

'In this example the Result is entered in Cell C1

Copy a folders content to another folder

http://vba-tutorial.com/copy-a-folder-and-all-of-its-contents/

' Copy Humm to Dude

' Change file names from old to new

Advanced Filters in a macro 1

Sub autoStockID()

' This will search and sort by Stock ID
On Error GoTo Line1

Dim x As Double
Dim rng As Range
Set rng = Range("A:E")
x = InputBox("Enter Product ID 5 Digit Number")
rng.autofilter Field:=2 ' This clears Field 2
rng.autofilter Field:=1, Criteria1:=x ' This filters the field to "x"

Columns(rng).AutoFit
Line1:
End Sub

Advanced Filters in a macro 2


On the "Data tab" you will find Advanced button.	For this set up use "Copy to another location".

A work around would be to hide the database columns "A:D"

Halt an Event.
The event macro below (UnProtect and Re-Protect) needed to be temporary halted to run another macro. We can temporary halt the event like this

' This turns the events off

Temporary Unprotect and Re-Protect the Sheet

In this macro we halted the running of the Private sub to autofit the rows. Then we turn back on the event.

' Here we unprotect the sheet

'Here were re-protect the sheet

Hide Columns with a macro XFD is the last column in Excel 2106

Hide rows with a macro

Dim lrow, x As Long
Dim rng As Range
lrow = Cells(Rows.Count, "A").Row
Set r = Range("A1:A" & lrow)
x = Application.WorksheetFunction.CountA(rng) + 1

UnHide rows with a macro

Hide / Unhide Sheets

Sub hide()
Sheet3.Visible = False
Sheet4.Visible = False
Sheet5.Visible = False
' You can hide as many sheets as you like.
End Sub

Sub Unhide()
Sheet3.Visible = True
Sheet4.Visible = True
Sheet5.Visible = True
End Sub

Highlighting a range that meet specific criteria using vba from activecell to an other cell

' change this to what ever the starting column number is

' This will clear the range of interior colors before resetting them.

' I am using Case instead of if statements

' This says if the case = "Facial" the select activecell and 4 rows same column will be highlighted color 20

' this will then offset by 5 and the macro will continue to search until the activecell is empty.

' This line is will select the activecell thru the activecell.offset(R1,C1)

Deleting sheets with VBA

Application.DisplayAlerts = False

Delete the last sheet

Copy and paste

Intelli Scense

Use ( Ctrl + Spacebar ) to open intelli scense at any time

Hi-Lite 4 rows - (Click here for video)

Filter by Font Size

'n is a column

' n is a range

UBound Function (Click here for two-dimensional array)

Move to next Tab and call a macro. (These 2 macros work together)

' This line counts the number of tabs in the workbook less one for the original page

' This moves to the next tab

' This calls the macro below for the new tab

' Copies current data to new location

' Adds 3 percent and copies back to original location

Worksheets and Sheets

This selects the exact sheet named "Sheet1"

This would error as there is no "Sheet2"

This refers to the second sheet from the left regardless of the sheet name. In this case Sheet(2) = Dan

Using a simple Math function without using a Function Statement

' Note answer is a math function

Scroll the Activecell to the "A1" Position

Convert Date to Text and Concatenate

' Using "Str", the date is now a string. Note it may need =trim

' C7 now displays the date as Text

' [c7] = "Today's Date is:" & str1 (Note - this can concantenate because both the text and the date are Text)

Concatenate cells W/ VBA

=EoMonth

Center Text with out using [Merge&Center]

Sub CenterAcrossColumns()
With Selection

.HorizontalAlignment = xlCenterAcrossSelection
.MergeCells = False

End With
End Sub

This acts like Merge and Center but does not merge the cells.

Working with Worksheet Functions ...... List of Functions that can be used in Macro's

More info on Macro Functions

Function and Sub - - - Adding Cells "A1" + "B1"

In this function we are determining if the activecell contains a Number or Not a Number, thus (a Letter) using a Function

Function CheckCell(CellValue) As Boolean
If IsNumeric(CellValue) Then
CheckCell = True
Else
CheckCell = False
End If
End Function

Sub izNumber()
Dim ReturnValue As Boolean

ReturnValue = CheckCell(ActiveCell.Value)
If ReturnValue = False Then
MsgBox ("Activecell is Not a number - Exit sub")
Else
MsgBox "The Activecell is a Number"
End If

End Sub

Sub iznumber()
Dim x As Variant
x = ActiveCell
If x >= 10 Then ' Can't say x <> isnumber (isnumber would have to be a variable in this instance)
MsgBox "Activecell is greater than or equal to 10"
Else
MsgBox "Activecell may be a number less than 10 or letter" & vbCrLf & "Contents of activecell is:" & ActiveCell
End If

End Sub

Note that the macro above will not work like the Function and Sub to the left.

If we change "Variant" to "Long" the macro will fail if the ActiveCell is a Letter.

So, the Function tells us what the ActiveCell is. Is it a Number or is it a Letter and the macro takes the appropriate action?

WorksheetFunctions Examples

Sub FunctionExample()
Dim n As Long
Dim x As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim D As Long
Dim E As Long
Dim F As Long
Dim G As Long
Dim diff as long

n = WorksheetFunction.Average(Range("D:D")) 'This section sums columns
x = WorksheetFunction.Sum(Range("D:D"))
a = WorksheetFunction.Sum(Range("D:D")) + 1
b = WorksheetFunction.Sum(Range("D:D")) - 1
c = WorksheetFunction.Sum(Range("D:D")) / 2
D = WorksheetFunction.CountA(Range("D:D"))
E = WorksheetFunction.Min(Range("D:D"))
F = WorksheetFunction.Max(Range("D:D"))
G = WorksheetFunction.Large(Range("D:D"), 2)

diff = Range("B1") - Range("A1") 'diff is like saying =sum(B1-A1) - use this to sum or minus + or / two cells

[i1].Value = x
[i2].Value = n
[i3].Value = a
[i4].Value = b
[i5].Value = D
[i6].Value = E
[i7].Value = F
[i8].Value = G
[i9].value = diff

End Sub

Shift a Column to the right

Sub insertCC()
Dim diff, iCntr As Integer
diff = Range("B1") - Range("A1") + 1
If Cells(1, 1) = "" Or Cells(1, 2) = "" Then ' Here we are using the "OR" command so if (Cell A1 or B1) is empty then

For iCntr = 1 To diff

' Note here we can Insert and Shift Column C moving data to the right

End Sub

Concatenating a Range of cells

Answer from web.


       Private Sub Trial_1_Click()

     Dim lr As Long

     

     lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

     Select Case lr - ActiveCell.Row

       Case 0: Range("P2").Value = ActiveCell.Value

       Case Is < 0: Range("P2").ClearContents

       Case Else: Range("P2").Value = Join(Application.Transpose(Range(ActiveCell, Cells(lr, ActiveCell.Column))), "")

     End Select

   End Sub

=Concatenate A1,B1 - - A1,C1 -- A1,D1

Copy the data from sheet 1 to sheet2 and transpose the data into columns
Set a & b range for column "A"
Goto an open cell and concatenate using "ActiveCell = a & b". Once the loop is complete Delete column "A" to shift all data to the left and start loop "P"
Once loop "P" is complete macro will copy all data and paste to sheet 3 using transpose to put data back into rows.


   Sub Concat1()

   Dim ws1 As Worksheet

   Set ws1 = Worksheets("Sheet1")

   Dim ws2 As Worksheet

   Set ws2 = Worksheets("Sheet2")

   Dim ws3 As Worksheet

   Set ws3 = Worksheets("Sheet3")

   Dim a As Range

   Dim b As Range

   Dim x As Integer

   Dim y As Integer

   Dim i As Long

   Dim lrow As Long

   Dim p As Long

   ws1.Range("A1").CurrentRegion.Copy

   ws2.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

   

   ws2.Select

   

   For p = 1 To 647

   lrow = Cells(Rows.Count, "A").End(xlUp).Row - 1

   x = 1

   y = 2

   Set a = Range("A" & x)

   Set b = Range("A" & y)

   [XX1].Select

   For i = 1 To lrow

   

   ActiveCell = a & b

   ActiveCell.Offset(1).Select

   y = y + 1

   Set b = Range("A" & y)

   Next i

   

   Columns("A").Delete

   Next p

   

   ws2.Range("A1").CurrentRegion.Copy

   ws3.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

   ws2.Range("A1").CurrentRegion.ClearContents

   ws3.Columns("A:XA").AutoFit

   ws3.Select

   MsgBox "Done!"

   End Sub

=Large(array,k)

' This is is the part you want to see

Working with Function =Countif(myRange, Cells(1,1))

In this example "myRange" is a Fixed Range.

Working with Function =Countif(myRange, Cells(1,1))

In this example "myRange" is a Dynamic Range.

Add Date and Military Time to Cell

' This will merge Cells A1:D1

'You may want to add a "With" statement to size and format the range

Click to see "WITH" Options

Add Date to cell A1 and Time to cell B1 non Military time

Add date and Time 12 hour clock using "With" statement

Lastrow

What is in the lastrow. (Answer 6)

What is the lastrow (Answer 3)

Asking the right question! Makes the difference.

Find in expanding box

Find Duplicates in a Column of numbers

' Need to change the Column from "D"

' Need to change the Column# where Cells(iCntr,4) is found

' Change the Column#('s) & Letters

' Need to change the Column number to next column to the right

Get Data From one workbook and move it to another workbook

Copy a Range using PasteSpecial - ( and not using Destination:= )

Copy Data from one workbook, from a dynamic range and paste to another workbook.

'This is the caregiver file

'This is the Master file

Setting the path

Dim sPath As String

sPath = ThisWorkbook.Path

ActiveWorkbook.SaveAs Filename:=sPath & "\" & sFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Workbooks.Open Filename:=sPath & "\" & oFilename & ".xlsm"
Workbooks.Open Filename:=sPath & "\" & sFileName & ".xlsx"

Chandoo

Become awesome in excel

Saving a file to the workbook path

' This is the path of the workbook.

Here is an example, Located at E:\Excel\NotCopyingLastWorkbook

Select a Range and Selecting and copying a range to a cell

' Copies "A1" thru the end of C

~~Range("A1").UsedRegion.copy~~

see below for UsedRange.

Copy and paste

When pasting in VBA use PasteSpecial and then Ctrl+spacebar for additional arguments

Example 1:

Destination

(This should work just as well as the line above it)

' This selects the cell on the activesheet

' This will paste as a value

Example 2: (Using Special Paste xlPaste Values in a Macro)

This will fail.

rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2).PasteSpecial xlPasteValue

This will work:

Copy and paste special (SpecialPaste Values)

wsO.ListObjects("Table2").DataBodyRange.Copy
wsI.Cells(Rows.Count, 16).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Copy and paste a range column and Transpose to a cell

Hide and unHide columns, using a Combobox - Combobox list and target located on Tab "List".

Copy the worksheet into new workbook and Save in a specific folder.

VBA Course Instruction in PDF

VBA Course home page

Click here: Why use "Option Explicit"

How to Password Protect your macro's

http://www.ozgrid.com/VBA/protect-vba-code.htm

How to protect and unprotect a pw protected worksheet or workbook (Tab Review protect sheet/Protect workbook)

How to Password Protect / Hide a worksheet and open it using a Macro .

Create a worksheet named “Menu” that serves as a menu page. On that page, add a button that will control the macro.
Create the macro, here is the Code.

For each worksheet change (-1xlSheetVisable) to (2-xlSheetVeryHidden)
in the VBA editor.

Finally step.
Password protect your VBA code as outlined in
(How to Password Protect your macro's.)
so that no one can open your vba editor and view the macro and its passwords.

Insert a Row

File picker

Column Name to Column Number

Use this macro to find the Column Number Like (A is 1) and (Z is 26) so what is JK? Use the macro to find out.

' To create a keyboard shortcut to this macro click on Developer/Macro/Options

Using TARGET

Note Target is used from the "Sheet" module and not the Module

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Row > 599 And Target.Column = 2 Then
Target.Offset(0, -1) = Date
End If
Application.ScreenUpdating = True
End Sub

Count number of Rows and Columns

' Last Row in Column "A"

' Last Column in Row 1

' Last used Column on worksheet (ws1)

' Last used Row on worksheet (ws1)

Count the number of non blank cells in a column. Or how many items values or text are in column 2

Create a Temp worksheet

Selecting a column with was chosen with Inputbox

Delete and or Clear Contents in a Row or Column

Loop, skipping cells. (More on Loops)

Case / Example

Last Row in Column "A"

Link to Microsoft Color Index

Link to Microsoft Color Index with examples

Change font color to red of all numbers in column "A" based on input number.

If Interior ColorIndex is then? - Hex to RGB converter

' So you may want to use these examples instead of using conditional formatting because
'you can not determine the interior color of a conditionally formatted cell

' This will change the interior color TO 11 = Dark Blue

' This will change the FONT color to 3 is red

' This will reset the cell interior to NO FILL

' This will reset the cell interior to White

' What the difference between -4142 and -4105
'-4105 fills the interior White so in you will not see the Guideline between "A1" and "B1" where as -4142 resets to no fill like a new sheet looks.

' This will clear the content of the cell

' This will clear the Formatting of the cell

' 56 = Dark Green

' 3 = Red - This changes the color of a range of cells.

What color is the ActiveCell ?

Setting the interior color of a cell based on Criteria

(----------------This could be use like conditional formating--------------)

Here we see Cells "A1", "C1" & "E1" changed to Green because those numbers were found in Column "G".

Todays date is (Variable Name (TDay))

' This will show Todays Date

Add Todays Date to cell A1 then add days to that date in cell below it.

Working with Dates ... Click here for more.

Sub addnumbers()
Dim startdate As Date
Dim newdate As Date
Dim r As Integer

startdate = Date

Range("A1") = startdate

r = 30
newdate = DateAdd("d", r, startdate)
Range("A2") = newdate

r = 31
newdate = DateAdd("d", r, startdate)
Range("A3") = newdate

r = 60
newdate = DateAdd("d", r, startdate)
Range("A4") = newdate

r = 61
newdate = DateAdd("d", r, startdate)
Range("A5") = newdate

r = 90
newdate = DateAdd("d", r, startdate)
Range("A6") = newdate

r = 91
newdate = DateAdd("d", r, startdate)
Range("A7") = newdate

End Sub

Delete Duplicates in a Range

.Range("A1:N" & .Cells(Rows.Count, 14).End(xlUp).Row).RemoveDuplicates

Download a txt file from the internet and save in file E:\Lotto. (used in Lotto_Fun 7)

E:\Lotto\Winnums-Text.txt

"http://www.powerball.com/powerball/winnums-text.txt"

E:\Lotto\Winnums-Text.txt

Create a new Worksheet using VBA

Create new Worksheets for all names listed in column "A" on Sheet("Sched")

Control a table filter with a macro.

Here is the setup by

Tabs:

The macro:

(This macro also needs the following macro to refresh the invoice list when something is added to the database)

Here is a newer example of controlling the filter

'This macro gets the data from table 1

' if you get an error change Double to String

' This macro gets the invoice numbers from table 1 then remove the duplicates so that we can use the number in a combo-box

'This macro resets the main data in table 1 on sheet "DB"

This was used in Excel File: " Autofilter_ControlledByVBA"

Slicer ... This will Clearing the slicer filter.

Clearing a Table Filters

Table Filter ... Setting a specific filter in a Field/Column

'Specifies David Duncan in field/column 1

'Specifies 39 in field/Column 2

AutoFilter with 2 criteria

"<" & date1 and ">" & date2

Range.AutoFilter Field:=3, Criteria1:= _
">" & date1, Operator:=xlAnd, Criteria2:="<" & date2

Reset AutoFilter on Table 1 in Field 3

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3

Select Current Region and remove duplicates

You will use this macro when you add a new item to the database Tab DB. Download the speadsheet here this was based on here.

How to copy and paste from one workbook to another workbook

Code for all 3 macros:

Look up a file location on the computer

' thiswill open the filed that you just looked up.

Range("A1:B10")

Open a Excel workbook (file) from an existing workbook

How to: Create / Delete a folder or delete a file in a folder

How to create a folder with a inputbox and then copy a file using the FileCopy from one folder to another using the Inputbox

Using CountA

Count even and odd numbers in a range using the MOD operator

Sub oddevn()

If Cells(1, 1) Mod 2 = "0" Then
MsgBox "Cells is Even"
Else
MsgBox "Cells is odd"
End If

End Sub

Functions (application.WorksheetFunction. ?)

List of Functions

Functions - VBA w/Sub Example:

More example of Functions.

Find more on Functions at: http://www.excel-easy.com/vba/function-sub.html

	A	B	C	D
1	10	6		4
2
3

Types of Variables

Name	Type	Details	Symbol
Byte	Numerical	Whole number between 0 and 255.
Integer	Numerical	Whole number between -32'768 and 32'767.	%
Long	Numerical	Whole number between - 2'147'483'648 and 2'147'483'647.	&
Currency	Numerical	Fixed decimal number between -922'337'203'685'477.5808 and 922'337'203'685'477.5807.	@
Single	Numerical	Floating decimal number between -3.402823E38 and 3.402823E38.	!
Double	Numerical	Floating decimal number between -1.79769313486232D308 and 1.79769313486232D308.	#
String	Text	Text.	$
Date	Date	Date and time.
Boolean	Boolean	True or False.
Object	Object	Microsoft Object.
Variant	Any type	Any kind of data (default type if the variable is not declared).

Scope of Variable

Hiding Rows

Example:
Sub hidrow()

Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer
Dim r As Integer
Dim box As Integer

BeginRow = 10
EndRow = 205
ChkCol = 2
r = 1

Application.ScreenUpdating = False

For box = 1 To 5 ' this represents the number of check boxes being used
For RowCnt = BeginRow To EndRow

If Cells(r, 1) = True Then
If Cells(BeginRow, ChkCol).Value = Cells(r, 2) Then
Cells(BeginRow, ChkCol).Rows.EntireRow.Hidden = True
End If
End If
If Cells(r, 1) = False Then
If Cells(BeginRow, ChkCol).Value = Cells(r, 2) Then
Cells(BeginRow, ChkCol).Rows.EntireRow.Hidden = False
End If
End If

BeginRow = BeginRow + 1

Next RowCnt

r = r + 1
BeginRow = 10
EndRow = 30
Next box

End Sub

If statement that stops or ends the macro operation.

If [a1] > "6" Then Exit Sub

Moving Up, Down, Left, Right

Find next blank cell in Column "B"

Cells(ActiveSheet.Rows.Count, 2).End(xlUP).Offset(1 ,0).Select

Fill in the "Blanks" or other value in a Contiguous range that contains blank cells

	A	B	C	D
1	Dan	Edie	Blank	Blank
2	Blank	Amanda	Bella	Blank
3	Blank	Blank	Blank	Patrick
4
5

Fill blanks with cell above

Start with:

Finish with:

Place the activecell in column "A".
Press "F5"
Choose Special
Click "Blanks"
Click "OK"
In formula bar type: =B2
Press: Ctrl + Enter

Select a Range and Selecting and copying a range to a cell

' Copies "A1" thru the end of C

~~Range("A1").UsedRegion.copy~~

Select a "CurrentRegion"

Current Region = A contiguous range of cells. Total and 100 will not be selected.

	A	B	C	D
1	Dan	Edie
2		Amanda	Bella
3				Patrick
4
5			Total	100

Select a "Used Range"

Used Range. All data is select regardless of the blank cells in Row 4.

	A	B	C	D
1	Dan	Edie
2		Amanda	Bella
3				Patrick
4
5			Total	100

Select Used Range and activate cell "B5"

	A	B	C	D
1
2		Dan	Edie
3			Amanda	Patrick
4				Bella
5

Using "With" to choose a cell and set a value in a cell

'Chooses cell "C5"

' Puts text "Enter Number" in cell "C5"

'Offsets from "C5" one cell down to "C6" and enter number "1"

'Offsets from "C6" and repeats 10 times one "Step" at a time

'Activecell is moved to location "C16"

'So it's like adding each of the dot lines to the original statement. Also note that we only used "Select" one time. This helps with keeping the macro fast.

Using "With" to format a range of cells

Sub FontBorderColor()

' Use this to set cell borders

End Sub

Sub colorOutSideBorderFont2()

' or xlThick

End Sub

Fill Down

Fill Down Example

Flash fill (Fill down)

Range("$A$1").flashfill ' This will fill down to adjacent column. Set up Column "B" enter 1 thru 10. Cell "A1" enter "1". Run macro.

Setting Row Height and Column Width

Goto last Row in a specified Column

This will take you to the last cell in column 2.

This will take you to the last cell in column 2 and offset to the next open cell down.

Goto last Row in column "A" using a (Control box using Sheet2 Cell B1 (Dan))

	A	B	C	D
1	Dan	Edith
2	Amanda
3
4
5

Goto last Column "C" cell using a (Control box using Sheet2 Cell B1(Dan))

	A	B	C	D
1	Dan	Edith
2	Amanda
3
4
5

Goto the end of a Row using a drop down list of names regardless of order.

DropDown list is located on Tab "Home" "Cell B3".

This can also paste a range to the end of the row.

Open a word docx from excel

Selecting cells with "Resize" and then taking action on them.

'Range("A1").resize(Row,Column).Select or ClearContents or what ever.
'Range("A1").resize(Rowsize:=4, ColumnSize:=4).Select 'Select from A1 4 row and 4 columns
'Range("A1").resize(2).Select ' Selects from A1 2 row or Cells A1 and A2

	A	B	C	D
1	Dan
2	Edith
3
4
5

Convert a range of formula's to Values

'Set the range to Column "D"

'Change the copied formula('s) from "C" to column "D" to values

Cells in column "C" are formulas. Once we copy the
formula to Column "D" we can now use the macro to
convert the formula's in column "D" to values.

On Error handling. (Read more about error handling)

' you can change 1 to any number.

' This will skip the error and continue on with the next line of code in the macro.

Example:

' there are no blank cells to fill so the error is skipped

'The macro continues from here.

Skipping lines inside a macro

Using Case

Test for Duplicates

Age What is my age

=sum(Todays Date - Birthday)/365.25

What does ME mean?

What is Me?

Events

' This will trigger when the WorkBook is opened.

MsgBox "Welcome Back!"

' This will trigger any time a cell has changed

' This will Trigger on an Calculation event

Searchable Dropdown List using ActiveX combobox

Click Here.

VBA Wait Application Method: Example 1

VBA Wait Application Method: Example 2

' I used this one in lotto xlPlus

VBA Wait Application Method: Example 3

VBA Wait Application Method2: Example 4 Less than 1 second

Custom formatting

You Tube Link
Basic custom formating

How Positive numbers should be formated ;
How Negitive numbers should be formated ;
How Zero's should be formated ;
How Text should be formated ;
# Hash tag is a numerical placeholder
; - SemiColin is as a separator
Up/Down Arrows. Get from "Inset tab" Symbols and place them in a cell somewhere. To use these synbols you must vopy them from the "Formula bar" and noto the cell you placed them in.
Adding color to the symbol
use square brackets [Color10] like this or to add any color from the Microsoft color pallet.

BAR Codes using excel

Substitute Split Numbers and Letters in a Serial number

Find_and_Goto_and_Find_Again

Move data from one workbook to another workbook

Find Next Tx

Find Cell by color

Find Cell by color

Change a range of cells Case to Upper, Lower or Proper

Find "+" in column "C" and shift data to the left

This example uses the Left function to return a specified number of characters from the left side of a string.

Open Windows Explorer to specific folder

Keyboard shortcuts

Uppercase a range with VBA

Print Page to PDF

Convert text to number

Find a cell by its color

Message Box Timer

Match for Row Number from the bottom up

Protected Sheet will not protect

Setting up a dynamic range using a table

How To Automate a Table in Word Using Excel

Copy from Excel to Word

Reset Dropdown lists

Reset Combo Box

Password Protect a Button

=REPT(0,8-len(A1))&A1 ...Put Zero's 0 in front of numbers.

While loop

Send email from excel

StockID Searchable Table

Scroll

Copy and paste End(xldown)

Copy and Paste a range

How to stop a running macro

BarCodes for Excel

After Enter Move to cell (Up, Down, Left, Right

Append & Remove data based on cell A1

Confirm the active cell is in column "A:A"

Intersect in a specific Range

Convert existing text that looks like a number to a number

Convert the String "ABCD1234" in Cell A1 to only the numbers "1234" in B1

VLookup in VBA

Using Match to find the row number of an item

Change UPPPERCASE / lower case / ProperCase

Force Upper Lower and Proper case when using a Inputbox.

Delete the active row using vba

Place a formula in a cell

Copy a folders content to another folder

Advanced Filters in a macro 1

Advanced Filters in a macro 2

Halt an Event. The event macro below (UnProtect and Re-Protect) needed to be temporary halted to run another macro. We can temporary halt the event like this

Temporary Unprotect and Re-Protect the Sheet

Hide Columns with a macro XFD is the last column in Excel 2106

Hide rows with a macro

UnHide rows with a macro

Hide / Unhide Sheets

Highlighting a range that meet specific criteria using vba from activecell to an other cell

Deleting sheets with VBA

Delete the last sheet

Copy and paste

Intelli Scense

Hi-Lite 4 rows - (Click here for video)

Filter by Font Size

UBound Function (Click here for two-dimensional array)

Move to next Tab and call a macro. (These 2 macros work together)

Worksheets and Sheets

Using a simple Math function without using a Function Statement

Scroll the Activecell to the "A1" Position

Convert Date to Text and Concatenate

Concatenate cells W/ VBA

=EoMonth

Center Text with out using [Merge&Center]

Working with Worksheet Functions ...... List of Functions that can be used in Macro's

More info on Macro Functions

Function and Sub - - - Adding Cells "A1" + "B1"

In this function we are determining if the activecell contains a Number or Not a Number, thus (a Letter) using a Function

WorksheetFunctions Examples

Shift a Column to the right

Concatenating a Range of cells

=Concatenate A1,B1 - - A1,C1 -- A1,D1

=Large(array,k)

Halt an Event.
The event macro below (UnProtect and Re-Protect) needed to be temporary halted to run another macro. We can temporary halt the event like this