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
Lastly, how to Unprotect an Excel Spreadsheet if you have lost your password.
