Archive for the ‘VBA’ Category
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first […]
Comments(1) Filed in categories: Excel, Regular expressions, User defined functions (udf), VBA
Thingspeak is web service that allows you to upload and store data from IoT devices. Picture above shows cheap chinese […]
Comments(0) Filed in categories: Excel, Regular expressions, User defined functions (udf), VBA
I got a question about counting background colors in a cell range. Excel uses two different properties to color cells and […]
Comments(0) Filed in categories: Excel, VBA
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a […]
Comments(1) Filed in categories: Excel, Regular expressions, User defined functions (udf), VBA
The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically […]
Comments(0) Filed in categories: Excel, Regular expressions, User defined functions (udf), VBA
Table of contents Basic schedule Round-robin tournament Double round-robin tournament Macro Download workbook How to use custom function According to […]
Comments(2) Filed in categories: Excel, User defined functions (udf), VBA
A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because […]
Comments(0) Filed in categories: Excel, User defined functions (udf), VBA
This post will teach you how to work with excel arrays in visual basic for applications (vba). Why do you […]
Comments(2) Filed in categories: Excel, VBA
Joe asks: Hello Oscar, I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate […]
Comments(2) Filed in categories: Excel, User defined functions (udf), VBA
The user defined function demonstrated below, resizes a range you specify to columns or rows you also specify. The first […]
Comments(1) Filed in categories: Excel, User defined functions (udf), VBA
This macro saves multiple selected sheets to a single pdf file. Make sure you select the sheets you want to […]
Comments(6) Filed in categories: Excel, VBA
Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in […]
Comments(12) Filed in categories: Excel, User defined functions (udf), VBA
The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]
Comments(6) Filed in categories: Excel, User defined functions (udf), VBA
Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato […]
Comments(14) Filed in categories: Excel, VBA
Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]
Comments(1) Filed in categories: Excel, VBA
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]
Comments(3) Filed in categories: Excel, Excel table, VBA
Today I would like to share to you these small event handler procedures that makes it easier for you to […]
Comments(9) Filed in categories: Excel, VBA
A twitter account belonging to NSA posted this weird message one morning in the beginning of May. It is a basic substitution cipher […]
Comments(4) Filed in categories: Excel, VBA
Macros are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is […]
Comments(3) Filed in categories: Excel, VBA
Today I will share a To-do list excel template with you. You can add text to the sheet and excel creates […]
Comments(7) Filed in categories: Check-boxes, Excel, Templates, VBA
If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]
Comments(2) Filed in categories: Excel, User defined functions (udf), VBA
Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it […]
Comments(9) Filed in categories: Excel, VBA
Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7. Instructions Here is how […]
Comments(2) Filed in categories: Excel, VBA
Click a single cell in column D to hide or show the comments in column E. Selecting multiple cells won´t […]
Comments(3) Filed in categories: Excel, VBA
Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. […]
Comments(0) Filed in categories: Excel, Excel table, VBA
UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]
Comments(26) Filed in categories: Conditional formatting, Count values, Excel, User defined functions (udf), VBA
Deleting empty cells in a cell range is easy. Select the cell range Press function key F5 Click "Special..." button […]
Comments(0) Filed in categories: Excel, VBA
Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson […]
Comments(10) Filed in categories: Charts, Excel, VBA
PRASHANT asks: i need to count unique number in a specific coloured cell for eg if there are coloured cells […]
Comments(10) Filed in categories: Excel, Unique distinct values, User defined functions (udf), VBA
The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]
Comments(2) Filed in categories: Conditional formatting, Excel, Overlapping, VBA
Right now most kids in Sweden are back in school after summer holidays. Their parents have had 4 to 6 […]
Comments(0) Filed in categories: Excel, User defined functions (udf), VBA
Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are […]
Comments(4) Filed in categories: Excel, VBA
The macro in this example adds series to a scatter chart. The first series is the dots and their names. […]
Comments(1) Filed in categories: Charts, Excel, VBA
A blog reader asks: I need an excel 2010 macro, where I can enter a number (value) into cell B1, […]
Comments(1) Filed in categories: Excel, VBA
Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]
Comments(16) Filed in categories: Excel, VBA
This post shows you how to move a shape with vba code. Select a cell (button name) and the arrow […]
Comments(7) Filed in categories: Excel, VBA
This post demonstrates how to quickly bring attention to a single bar in a chart. Highlighted bars in two or […]
Comments(4) Filed in categories: Charts, Excel, VBA
Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]
Comments(6) Filed in categories: Excel, User defined functions (udf), VBA
You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]
Comments(17) Filed in categories: Drop down lists, Excel, VBA
Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]
Comments(5) Filed in categories: Charts, Excel, Excel table, VBA
Peter asks: Hello, I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 […]
Comments(1) Filed in categories: Charts, Excel, VBA
Table of Contents Copy each sheet in active workbook to new workbooks Copy selected sheets to new workbooks Copy sheets […]
Comments(4) Filed in categories: Excel, VBA
Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]
Comments(1) Filed in categories: Excel, Excel table, VBA
I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined […]
Comments(5) Filed in categories: Drop down lists, Excel, User defined functions (udf), VBA
Rick Rothstein commented: Redim Preserve does not execute all that quickly, so it is usually a good idea to avoid […]
Comments(7) Filed in categories: Excel, VBA
Jinesh asks: I have to combine 200 columns into one list. I know. I tried steps from 'Combine cell ranges […]
Comments(3) Filed in categories: Excel, VBA
The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each […]
Comments(5) Filed in categories: Excel, Sort values, VBA
The following macro let´s you select a cell range and then the macro creates sheets with the same names as […]
Comments(0) Filed in categories: Excel, VBA
awall asks: Hey can you do the opposite of this - not random order but this is my situation. i […]
Comments(7) Filed in categories: Excel, Sort values, VBA
Here is another macro to normalize data. Scenario: The user has entered multiple values in the same cell. The macro […]
Comments(0) Filed in categories: Excel, VBA
It can sometimes be helpfull having a large cell value in a comment. You can then easily read the value […]
Comments(0) Filed in categories: Excel, VBA
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Comments(4) Filed in categories: User defined functions (udf), VBA
In this blog post I am going to demonstrate a simple workbook where you can create or delete projects and […]
Comments(10) Filed in categories: Excel, Templates, VBA
I read this post Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel […]
Comments(3) Filed in categories: Excel, Pivot table, VBA
Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]
Comments(11) Filed in categories: Excel, Pivot table, VBA
Phil asks: Could you please show me the code to place the copied data into a different tab instead of […]
Comments(6) Filed in categories: Excel, VBA
Cyril asks: I do remember seeing one nice way of populating a table with the use of vba such as […]
Comments(4) Filed in categories: Excel, VBA
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Comments(6) Filed in categories: Concatenate, Drop down lists, Excel, VBA
Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I […]
Comments(5) Filed in categories: Excel, VBA
The macro provided in this blog post let´s you copy table design settings to other tables in the same workbook. […]
Comments(0) Filed in categories: Excel, VBA
Rodney Schmidt asks: I am a convience store owner that is looking to make a spreadsheet formula. I want this […]
Comments(2) Filed in categories: Combine/Merge, Count values, Excel, Pivot table, VBA
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What i would really like to […]
Comments(8) Filed in categories: Drop down lists, Excel, VBA
In some cases it can be useful and timesaving to automate data entering. The vba examples here all enter a […]
Comments(8) Filed in categories: Excel, VBA
This post demonstrates how to automatically create log entries when a workbook opens or closes. VBA code Where to copy […]
Comments(3) Filed in categories: Excel, VBA
Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, […]
Comments(16) Filed in categories: Excel, VBA
In this blog post I will demonstrate some vba copying techniques. Example 1, This example code copies cell range A1:B2 […]
Comments(0) Filed in categories: Excel, VBA
In this post I am going to demonstrate how to create a new sheet in the current workbook and list […]
Comments(1) Filed in categories: Excel, VBA
In this small tutorial I am going to show you how to create basic data entry with a small amount […]
Comments(4) Filed in categories: Excel, VBA
This macro creates a new sheet and lists all tables and corresponding table headers in a workbook. Example Sheet1, 2 […]
Comments(1) Filed in categories: Excel, VBA
In this post I am going to demonstrate how to quickly apply a filter to a table. I am using […]
Comments(1) Filed in categories: Excel, VBA
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Comments(17) Filed in categories: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA
This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]
Comments(30) Filed in categories: Check-boxes, Excel, VBA
In this post I will demonstrate how to create checkboxes in nonempty rows. VBA code Where to copy vba […]
Comments(3) Filed in categories: Check-boxes, Excel, VBA
In this tutorial I am going to show you how to create a drop down list (data validation) in cell […]
Comments(2) Filed in categories: Excel, VBA
This post describes how to lookup values in multiple cross reference tables using a user defined function. This custom function […]
Comments(3) Filed in categories: Cross reference table, User defined functions (udf), VBA
This post describes how to add a new custom-built item to the shortcut menu in excel 2007. In excel 2007 […]
Comments(0) Filed in categories: Misc, VBA
This post describes a basic user defined function that searches multiple ranges and adds corresponding values. […]
Comments(1) Filed in categories: Excel, Misc, User defined functions (udf), VBA
This post demonstrates how to automatically select cell A1 on each sheet in a workbook before you close a workbook. […]
Comments(5) Filed in categories: Excel, VBA
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
Comments(3) Filed in categories: Excel, User defined functions (udf), VBA
I have noticed when I apply conditional formatting on larger cell ranges, file size increases rapidly. This post demonstrates how […]
Comments(0) Filed in categories: Conditional formatting, Excel, VBA
This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]
Comments(4) Filed in categories: Combine/Merge, Excel, User defined functions (udf), VBA
This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]
Comments(16) Filed in categories: Count values, Excel, Frequency, User defined functions (udf), VBA
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Comments(2) Filed in categories: Excel, User defined functions (udf), VBA, Vlookup
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Comments(3) Filed in categories: Combine/Merge, Excel, VBA
This blog post describes how to create text qualifers and make "text to columns" conversion easier. Example I copied a […]
Comments(2) Filed in categories: Excel, VBA
A previous post described how to create a listbox filled with unique invoice numbers: Excel vba: Populate listbox with unique invoice […]
Comments(3) Filed in categories: VBA
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
Comments(3) Filed in categories: VBA
This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]
Comments(40) Filed in categories: Excel, VBA
Introduction This blog post describes how to automatically select a value based on time and date using vba in excel. […]
Comments(0) Filed in categories: Dates, Excel, VBA
Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]
Comments(3) Filed in categories: Duplicate values, Excel, Unique distinct values, User defined functions (udf), VBA
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Comments(11) Filed in categories: Excel, Unique distinct values, User defined functions (udf), VBA
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Comments(2) Filed in categories: Duplicate values, Excel, User defined functions (udf), VBA
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]
Comments(11) Filed in categories: Combine/Merge, Excel, VBA
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
Comments(14) Filed in categories: Advanced filter, Excel, Unique distinct values, VBA
Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]
Comments(24) Filed in categories: Excel, VBA
