Repeat Values N Times

less than 1 minute read Here is a method to repeat the values in Column ‘A’, (n) number of times required in Column ‘B’ outputting the results in Column ‘C’. The Formula is an Array Formula requiring you to press CTRL+SHIFT+ENTER. So with a list in Column A and the respective ‘n’ times to repeat … Read more

Function To Test If The Active Sheet Is Protected

less than 1 minute read Function to tests if the active sheet is protected. ‘============================================================================================ ‘## Tests if the activesheet is protected ‘ ‘ If the activesheet is protected ‘ If ActiveProtected = True Then Exit Sub ‘============================================================================================ Function ActiveProtected() As Boolean ‘ If the activesheet is protected then exit sub If ActiveSheet.ProtectContents = True … Read more

Clean Data With Vba

1 minute read What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleaning (removing unprintable characters) process for your Excel data. It’s a great way to clean up your data getting exported from an outside database. ‘================================================================================================== ‘ ## Clean … Read more

Inputbox Type 0 Get Formula From Range

less than 1 minute read InputBox Type 0 – get formula from range ‘================================================================================================== ‘ ## InputBox Example 6: (Type 0) – Inputbox to get formula from range ‘================================================================================================== Sub InputBoxFormula() ‘// Vars Dim YourFormula As Variant ‘// Get formula from cell YourFormula = Application.InputBox _ (Prompt:=”Get the formula”, Title:=”Formula example”, Type:=0) ‘// Test if … Read more

Custom Number Formats

Custom number formats control how numbers are look in Excel. The key benefit is that they change how a number looks without changing any data. Before we get started, here’s an awesome resource Excel Custom Format Builder This concept is used most frequently with dates. Input Code Result 20-Apr-2020 yyyy 2020 20-Apr-2020 yy 20 20-Apr-2020 … Read more

Inputbox Type 4 Get A Boolean Response

less than 1 minute read InputBox Type 4 – get a boolean response ‘================================================================================================== ‘ ## InputBox Example 7: (Type 4) – Inputbox to get a boolean response ‘================================================================================================== Sub InputBoxBoolean() ‘// Vars Dim blnAns As Boolean ‘// Boolean prompt, 1 = true, 0 = false blnAns = Application.InputBox(Prompt:=”Acceptable Answers: 1/True or 0/False” & vbCr … Read more

Remove Page Break Lines In All Workbooks

less than 1 minute read Loop through each sheet in all open workbooks and remove the page break lines Sub DisablePageBreaks() ‘// vars Dim Wb As Workbook Dim Sht As Worksheet ‘// optimise Application.ScreenUpdating = FALSE ‘// loop thruogh sheets and hide page breaks For Each Wb In Application.Workbooks For Each Sht In WB.Worksheets Sht.DisplayPageBreaks … Read more

Force Saveas Ui To Only Accept Macro Enabled Workbook (xlsm)

less than 1 minute read Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook. ‘========================================================================================================== ‘ ## Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook ‘ (1) If a workbook is already saved as xlsx then this code is added then clicking save ‘ will prompt the user that workbook … Read more