Paste Values With Vba With A Keyboard Shortcut Permalink
This will show how to use VBA to paste values in Excel instead of formulas. You can bind this VBA macro to the Ctrl+Shift+V keyboard shortcut to make pasting...
This will show how to use VBA to paste values in Excel instead of formulas. You can bind this VBA macro to the Ctrl+Shift+V keyboard shortcut to make pasting...
This VBA Environment function grabs information about your operating system and returns the information as a string. The Environ function is useful for custo...
When you have a SharePoint Document Library and use the Export to Excel feature, you may have noticed that document versions will not be exported to the work...
InputBox Type 4 - get a boolean response
InputBox Type 0 - get formula from range
InputBox - Assign an array of Values to a Range
InputBox Type 8 - show how to get the selected range address
InputBox Type 8 - select a range and write values to each cell ```vb ‘======================================================================================...
InputBox Type 2 - get some text from the user
InputBox Example 1: (Type 1) - Inputbox to get a number from the user.
This uses the StartImportCSV module to call the ImportCSVFile function, with set parameters to import a csv file. You can muck around with this and use other...
Hyperlink a file or folder using the HYPERLINK formula in the active cell using VBA. This method pulls up a file picking dialog to choose a file or folder (...
An easy user defined function (UDF) to get the nth word from cell. I often use this to get first and last names when the full name is in a single cell.
Pull data from a Closed Workbook Pass the Filepath, Filename, Sheet Name, Range and the Sheet that you want to pull the Data into. Call the Function, the Ran...
Here are some basic methods to get a Column Letter & Row Number from both parts of a Range ie. “A1:B2”. The examples below will result in Column “A”, Row...
Manually typing each option for a drop-down list in word (drop-down list content control) is time consuming, but there is no native way to import a list you’...
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...
Function to test if the workbook is protected.
Function to tests if the active sheet is protected.
Tests if any sheets in the active workbook are protected with or without a password.
Function to test an entire column or row has been selected.
User Defined Function to get the unique count in a range.
Function to get the selected sheet names with examples.
Function to check if multiple named ranges exist
When you want to just show numbers as 9.1 M or 830 K
Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook.
Examples on how to first the last row, first available blank row or the last blank row
User can save the new workbook as file types: xlsx, xlsm, xlsb, xls, csv and txt with the following features: external links are broken formulas that re...
Some examples of VBA constants, useful when developing programs / add-ins.
Example of Page Setup for the active sheet for printing example has a header, footer, narrow margins and the Column width to the page but not the rows.
Delete the selected row in the active table will prompt the user that the selected cell in a table is about to be deleted and to Yes to confirm as deleting w...
Here I outline lots of ways that you can delete blanks rows in any chosen column.
These piece of code lets you delete an entire row within the used range of the sheet if the entire row contains no data.
This is one of my favourites and I use it all the time. Type a bunch of sheet names in a list of cells, select those cells then run this macro.
This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.
The following macro will hide all the column containing the word “hide” in each cell in row 1. Here is a brief description of how the code works:
The below 2 examples create a named range for either a workbook scope or worksheet scope. Below the subroutines is more information on the differences and ad...
This will copy formulas from one place to another and then Copy & Paste the calculated results as Special Values There are 2 components, one is the long ...
Copies only the visible cells in a selected range to a new workbook, a A1
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 R...
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 ...
This joins many addresses together as a string, picks up a defined named range “sNamedRange” and clears the contents from the cells.
What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleani...
Force the userform to load to the centre of the active excel window, add this code to the userform Initialize event. Perfect for when you have more than 1 ac...
Good if you are running a macro that will affect cell values
A technique to add a custom right click menu (context menu) to a text box on a UserForm. There is no system context menu for text boxes in a userform, and th...
You will be prompted with an input-box for the range of cells and for the leading text.
Add a row at the bottom of the active table. This will test that the active cell is in a table, then add a single row to at the bottom.
A whole heap of declarations for 32 and 64 bit operating systems.
This one is pretty sweet. It creates a nicely formatted TOC sheet and a back button on each sheet that links to the TOC. If you happen to add a sheet, simply...
Join multiple string cells using one of 2 options, the code below shows how to choose the two, it boils down to what the user finds easier to read.
Loop through each sheet in all open workbooks and remove the page break lines
Sub ReadOnlyCheck() Dim Answer As Long 'Check to see if file is Read-Only If ThisWorkbook.ReadOnly = TRUE Then Answer = MsgBox("Someone Is curren...
Loop through all tables in the workbook
Snippets to loop through control types or a specific control type. Especially useful for bulk actions.
This code snippet is useful to check if the selection is a range of cells.
Easily add a custom right click menu for both normal ranges and in tables! First we need to put two codes into the ThisWorkbook Module, these 2 codes call ma...
Here’s the simple steps to highlight the row and column of the selected cell which can be extremely useful when navigating large sets of data. Here’s a littl...
How it Works Using SEARCH and ISNUMBER When you need to check if a cell contains specific text (or string) we need to combing the SEARCH and ISNUMBER functio...
So you have a 0% value on one of your data labels and want to hide it?
Download the example workbook here: calculate-time-between-dates.xlsx
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...
What Are Custom Number Formats?
Here’s a simple way to get the row number of the table items. It’s a simple formula referencing the current cell’s row minus the row number of the table head...
What is an Excel table?
Have you ever been in a VLOOKUP hell?
Use Conditional Formatting from the Format menu to apply a format to the cells. The formula used in the Conditional Formatting rule is based solely on the ro...
There’s no built in way to count the number of words in Excel, but using the following formulas will get it done.
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 ...
How to split first and last name from full name with space
These formulas are great for when you have to find the week ending date or even the fortnight ending date for a specific item. Week ending dates don’t have a...
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the...
If we had to calculate Total Sales the normal way, we would have to create a ‘helper column’ for the Totals column and then enter a formula to Sum all the To...
From a list of 30 people with an assigned value, generate a smaller list that are the top n values in that list.
There are a few ways to convert Numbers stored as Text using Formula. You can use: =VALUE() or =NUMBERVALUE() for floating point Numbers. =INT() if you ...
Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function.
Let’s image that the following path is in A1: C:\Users\foo\Documents\parent.folder\foo.bar.txt
Convert text to lower case Example: =LOWER("hello") Result: hello
Convert text to lower case Example: =LOWER("hello") Result: hello
You can always use the Insert –> Symbols method to enter characters onto your spreadsheet but you can also add them via a formula.
There are 2 formulas to calculate percentage variance (change).
These formulas require that the workbook is saved
We can expand on the Index and Match formula and use an array formula to look up a value based on multiple criteria. This is an array formula so need to pre...
These formulas require that the workbook is saved
Generate a list of items based on a criteria that doesn’t show any blanks or spaces. 20 Fruits and Vegetables listed below, choose either a fruit or vegetabl...
This tutorial demonstrates the key strengths of Excel’s INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples t...
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...
When converting a decimal to hours and minutes, ensure to use the correct Number Format.
Combine these 3 functions to completely clean your data: =TRIM(text) =CLEAN(text) =SUBSTITUTE(text,” “,””)
When checking if a value exists in a range, using COUNTIF is quicker, easier and less prone to errors. This table shows the returning values for each formula...
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use ...
Summary SUMPRODUCT is an incredibly versatile function that can be used to sum and count like SUMIFS or COUNTIFS, but with more flexibility.
Link to all xlsx Example Files: https://github.com/ExcelTitan/Excel_Formulas
Sample Data Table of Sales
List of Australian States and their Capital Cities.