Some useful VBAs

Below are some useful VBAs I have recently used in by spreadsheets. I am publishing them as it may help others struggling with VBA code as I often do 🙂

Sub HideRows()
    ‘This command hides all the rows from 2 to 100.
    ‘I have not hidden row 1 as it has my headings.
Rows(“2:100”).EntireRow.Hidden = True
‘This command unhides the active row
Rows(ActiveCell.Row).EntireRow.Hidden = False
End Sub

Sub UnhideRows()
    ‘This command Unhides all the rows
Rows(“2:100”).EntireRow.Hidden = False
End Sub

How to assign short keys?

By assigning keyboard shortcut keys you can make is easier to run these macros.
Developer > Macros >Options> Shortcut Key

Below is a sample macro I have created for a daily task.

Sub BankFlash()

   ‘Define the variable for sheet name
Dim sname As Single

    ‘Assigning the variable with the value of Active Sheet’s number
sname = ActiveSheet.Name

    ‘ Copying Active Sheet and then telling it to paste just after the active sheet
ActiveSheet.Copy After:=ActiveSheet

    ‘ Now the Active sheet is the new sheet and we are increasing the sheet name by 4 if its  a Monday.   

If Weekday(Now) = 2 Then
ActiveSheet.Name = sname + 4
Else

‘ On days other than Monday, we increase the sheet name by 1   
ActiveSheet.Name = sname + 1
End If

‘Active sheet is the new sheet now.
‘Clears contents from specific cells in the new sheet
Range(“B7:d12”).ClearContents
Range(“B17:d24”).ClearContents

‘Clears contents from specific cells in the new sheet if today is Monday
If Weekday(Now) = 2 Then
Range(“H19:H23”).ClearContents
Else
End If

‘Links the opening balance to the previous sheet             

Range(“B4”) = “=” & Str(sname) & “!B29”
Range(“C4”) = “=” & Str(sname) & “!C29”
Range(“D4”) = “=” & Str(sname) & “!D29”

‘Last Step , this leaves the control on the box from where i wish to start inputting data
Range(“B31”).Select

End Sub

Custom functions

The below macro creates a FUNCTION for excel to add numbers by colours. I still donot know how it works fully but I find it useful sometimes.

Function SumByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
End If
Next TCell
End Function

Bonus

1. how to Unprotect an Excel Spreadsheet if you have lost your password.

2. Sometimes while using filters dates donot group together. Simply multiply all dates by 1 , using paste special function.