Function To Test An Entire Column Or Row Has Been Selected

1 minute read Function to test an entire column or row has been selected. Using ths function before commencing your macro is a great idea if the macro is using the selection as a variable range. ‘============================================================================================ ‘ ## Checks whether an entire column or row has been selected and notifies the user ‘ ‘ … Read more

Hide Zero Values In Data Labels

less than 1 minute read So you have a 0% value on one of your data labels and want to hide it? The quick and easy way to accomplish this is to custom format your data label. Select a data label. Right click and select Format Data Labels Choose the Number category in the Format … Read more

Calculate Time Between Dates

1 minute read Download the example workbook here: calculate-time-between-dates.xlsx Calculating hours, minutes and seconds between two times =IF(B2< A2, 1 + B2 – A2, B2- A2) Number Format: h:mm:ss Calculate days To calculate elapsed days is so easy, you just need to apply this formula Number Format: Calculate Days, Hours and Minutes To calculate and … Read more

Function To Test If The Workbook Is Protected

less than 1 minute read Function to test if the workbook is protected. ============================================================================================= ‘ ## Tests if the activeworkbook is protected ‘ ‘ Test if the workbook is protected ‘ If WorkBookProtected = True Then Exit Sub ‘============================================================================================ Function WorkBookProtected() As Boolean With ActiveWorkbook If .ProtectWindows Or .ProtectStructure Then WorkBookProtected = True MsgBox “This … Read more

Converting Column Numbers To Letters

1 minute read Obtaining Row information is easy since Rows are always Numbers. Column Letters that can be used in a Range are a little more tricky. I have many methods to obtain a Column Letter from a Column Number here are a few of my favourites: ‘================================================================================================== ‘ ## Function to get the column … Read more

Copy A Formula Down A Dynamic Column Range

1 minute read Copy Formula down a dynamic Column Range with or without AutoFill or using FillDown You can use the following methods to Copy Formula down a dynamic Column Range (assumes data is in Column A with a Header & Formulas are in Columns “B:D”): Dim lngLastRow As Long lngLastRow = Range(“A” & Rows.Count).End(xlUp).Row … Read more