Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Total" IN COLUMN A
'========================================================================
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "Total" Then
Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
'Cells(i, "A").EntireRow.Delete ' Use this to delete the entire row
End If
Next i
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Tax" IN COLUMN A
'========================================================================
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "Tax" Then
Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
'Cells(i, "A").EntireRow.Delete ' Use this to delete the entire row
End If
Next i
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Grand Total" IN COLUMN A
'========================================================================
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "Grand Total" Then
Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
'Cells(i, "A").EntireRow.Delete ' Use this to delete the entire row
End If
Next i
'========================================================================
'Delete extra columns from Column "K" over ***Change as Needed***
'========================================================================
'Columns("K:K").Select
'Range(Selection, Selection.End(xlToRight)).Select
'Selection.Delete Shift:=xlToLeft
'------------------------------------------------------------------------
Rows("1:5").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "General"
Columns("C:D").Select
Selection.EntireColumn.Hidden = True
Rows("6:6").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
With Selection.Font
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "SIMPLY RYAN"
Range("A2").Select
ActiveCell.FormulaR1C1 = "KC NBR HOUSE TYPE"
Range("A3").Select
ActiveCell.FormulaR1C1 = "ROOM TYPE"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A4").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:A2").Select
Selection.Font.Bold = True
Columns("B:B").ColumnWidth = 6
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Range("E1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'========================================================================
'Auto Total Columns
'========================================================================
Dim TotRw As Long
Dim UsdCols As Long
TotRw = Range("A" & Rows.Count).End(xlUp).Offset(2).Row
UsdCols = Cells(6, Columns.Count).End(xlToLeft).Column
Range("A" & TotRw).Value = "Total"
Range("E" & TotRw).Resize(, UsdCols - 4).FormulaR1C1 = "=sum(r7c:r[-2]c)"
'========================================================================
'Add Shading Automatically
'========================================================================
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set StartCell = Range("E6")
'Find Last Row and Column
LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = Cells(StartCell.Row, Columns.Count).End(xlToLeft).Column
'Select Range
Range(StartCell, Cells(LastRow, LastColumn)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'========================================================================
'Shade NA Cells
'========================================================================
Selection.FormatConditions.Add Type:=xlTextString, String:="NA", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = True
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'BEGINNING OF OPTIONS MACRO
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'========================================================================
'ID Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=4" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 4"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=5" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 5"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=6" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 6"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=7" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 7"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=8" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 8"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=9" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 9"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=10" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 10"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=11" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 11"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=12" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 12"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=13" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 13"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=14" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 14"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=15" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 15"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=16" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 16"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=17" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 17"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=18" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 18"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=19" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 19"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=20" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 20"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=21" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 21"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=22" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 22"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=23" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 23"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "ID=24" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", ID 24"
End If
Next
'========================================================================
'RD Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=4" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 4"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=5" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 5"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=6" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 6"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=7" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 7"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=8" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 8"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=9" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 9"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=10" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 10"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=11" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 11"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=12" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 12"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=13" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 13"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=14" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 14"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=15" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 15"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=16" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 16"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=17" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 17"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=18" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 18"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=19" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 19"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=20" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 20"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=21" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 21"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=22" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 22"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=23" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 23"
End If
Next
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "RD=24" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", RD 24"
End If
Next
'========================================================================
'MI Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "MI" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", MI"
End If
Next
'========================================================================
'FEDEP Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 3) Like "*" & "FEDEP" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", FE"
End If
Next
'========================================================================
'OFD Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "VD=OFD" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", OFD"
End If
Next
'========================================================================
'MFD Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "VD=MFD" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", MFD"
End If
Next
'========================================================================
'GLS Search
'========================================================================
lRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
For i = 2 To lRow
If Cells(i, 4) Like "*" & "=Clr" & "*" Then
Cells(i, 1) = Cells(i, 1) & ", Clear"
End If
Next
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'End OF OPTIONS MACRO
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'========================================================================
'Auto Fit Column "A" after Modifications are Added
'========================================================================
Columns("A:A").EntireColumn.AutoFit
End Sub