Category Archives: VBA Examples and reference

VBA to check if any cells on the row have a colour

This quick script will scan through the worksheet(“X”) and if there is a coloured cell on the current row, it will put a 1 in the first empty column so you can filter easily. Make sure you change the name of the worksheet in the code to reflect the one you are checking. ‘Speed up […]

Check if cell is a number

This quick piece of code will run down the current sheet and if the value in the cell is NOT a number or is BLANK it will give you a messagebox of what the value is. This is useful for when you want to determine if a particular cell has a number, and if not […]

Create a PDF from Excel workbook or range

Creating a PDF directly from an Excel worksheet is simple with the following VBA Example: FName = “Datanology Dashboard Example” Sheets(“PDF”).Select Range(“A1:BP89”).Select Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ “C:\Datanology\” & FName & “.pdf”, Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False _ , OpenAfterPublish:=True

VBA Assign a Hyperlink to a shape

Once you’ve drawn your shape and added text if applicable, or added an image that you want a hyperlink assigned to you can use this VBA example. It is particularly useful to add Hyperlinks this way if the hyperlink needs to change address throughout your VBA script running. For instance you may have a list […]

Getting the First day of the month in VBA

This code should be added within a module to assign the first day of the month to the variable: fst_crnt_mnth act_dte = Now() – Day(Now()) + 1 ‘Gives you the first day of the current month

Images in Userforms from file or web

Userforms in Excel can give a professional look and feel to any Excel project, database or data entry system. Several of our recent projects include dynamic pictures where the clients images are used to show stock from their own PC or directly from a webpage. Either way can be used to show any image from […]

Split list of names and add title

A recent project brief managed by datanology related to the agreed marketing data of a companies client list. In order to create a more formal yet personal approach, the list of 30,000 names needed to have the title (Mr / Ms) prefixed and to assist in the recording of the emails sent out, the first […]

MAC VBA Workarounds – Listindex

Listindex does not work on Mac VBA in Excel. It’s a shame because it can be a very powerful function when dealing with Comboboxes in Userforms for instance. If you want to know the order in the list that a chosen value is held so you can populate other parts of the form, or lookup […]

Loop through all sheets and copy used range to bottom of specific tab

Sub loop_sheets_and_copy_used_range() Dim WS_Count As Integer Dim I As Integer With Worksheets(“Main”) lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row End With ‘ Set WS_Count equal to the number of worksheets in the active workbook. WS_Count = ActiveWorkbook.Worksheets.Count ‘ Begin the loop. For I = 2 To WS_Count With Worksheets(I) .UsedRange.Copy Destination:=Worksheets(“Main”).Range(“A” & lastrow + 1) End With With […]

Get forename and surname from delimited full names

One way of identifying the first and last names in a cell is to use text to columns function and delimit the cells in the column where the name is, by space. This is great in circumstances where only the first name and surname are in the cell to begin with. Once we start having […]

Split text by hard returns in a cell

Split Excel data

If you have a dataset in Excel or has perhaps been copied over from Word where multiple lines appear in a single Excel cell, it can be difficult to split the text out into a useable format. The example below was used in a recent project for a client who needed not only to split […]

Loop through worksheets and extract specific cells to a new worksheet

There may be an instance where you have potentially hundreds or even thousands of worksheets in a workbook, or have collated a large number of identical worksheets together and need to extract specific cells in each sheet and store them in a table or each sheets own row on 1 main worksheet. The VBA script […]

Populate Combobox from csv string in a cell

There are many different ways to populate the data in a combobox in a Userform – using lists on a lookup tab, defining each single one in hard code or running through a column to look for specific values and when finding said value add an item offset from that column etc. However, one of […]

Generate Random text password

Datanology are providers of offline, credit based UK postcode lookups to enable users to validate customer address data and clean their datasets in bulk. Our systems allow users to cleanse, validate and standardise the street name, town/city & county in large datasets of customer addresses. It also allows customer databases to use lookups to auto […]

Identify different colour letters in a string

Having researched many of the Excel help sites, we have not been able to find a solution for splitting out text in the same cell where a colour changes, or a string is a particular colour within a string. A client required this solution, and after several hours of trialling different situations, we have created […]

Make only specific text in a cell bold

Attempting to create part of a string in a Userform textbox proved not yet possible in Excel up to version 2016, so an alternative function was created for a client to allow them to copy a string of text from a userform and in the process cause specific sections to be set to bold. The […]

Send entire row to text file on condition of cell value being Y

This code was used on a project recently to allow data from a worksheet to be output to a csv file with the name Output_ and todays date without the ‘\’s. The code is a good example of taking the data if the value in column AS is ‘Y’ or ‘y’. It also adds the […]

Delete row if one of the specified values are not identified on that row, column A

If you have a large list or dataset and need to keep particular rows of data rather than select those you don’t want you can select the ones you want to keep and use this example below to create a script to remove all but these values specified. Adjust the code going from the bottom […]

Value of merged cell area in Excel

Merged cells in Excel are known to cause inaccuracy issues with lookups, matching etc and can be a problem area for users analysing data or calculating against cells where the value is in the middle of a merged area of cells. Datanology have recently completed a project related to Amazon Postage and storage calculations where […]

Assign matching codes to same items in a list

Quite a straightforward requirement, but none-the-less if your dataset is quite large, repetitive pasting of values into multiple cells in a column can become monotonous, very labour intensive, and the more you have the more prone to errors and inaccuracies or missing data. In the example below we only use 44 rows of data, but […]

Delete rows that contain a special character in the text

When cleaning data or manipulating a dataset in order to upload into an e-commerce website for instance, the data must be absolutely spot on, accurate and adhere to the websites data standards. This might mean that data with special characters such as !”$%^&*()_+ for instance may not be allowed within the data. This could cause […]

Import multiple csv files to 1 column in 1 sheet

Application.ScreenUpdating = False Worksheets(“Sheet1″).Cells.ClearContents Dim qry As QueryTable Dim FilNams As Variant Dim FilNamCntr As Long Dim strQryName As String Dim LastRow As Long Dim ContainerWB As Workbook Dim msgString As String Dim rng As Range Dim iCol As Integer Dim lastCell As Integer FilNams = Application.GetOpenFilename(FileFilter:=”Text Files (*.csv),*.csv”, _ Title:=”Select Textfile to Import”, _ […]

Import all worksheets from a chosen workbook

This code example will present you with a dialog box where you can choose which Excel (.xls or .xlsx) workbook you want to import into your current workbook. It is assuming that you are running this macro from a worksheet named ‘Sheet1’ or you at least have a worksheet named ‘Sheet1’ in the workbook. This […]

Excel connect to a COM port

Connecting to a COM port gives your Excel project functionality for external devices such as Barcode readers, key button readers such as point of sale login widgets, temperature sensors etc. A straightforward example script to manage the communications between the device and Excel is provided below, and as an example of what devices can be […]

Auto select textbox contents on mouse over

Datanology’s Data Mining projects, extracting the useful and valuable data from large sets of data uses this kind of process regularly, in conjunction with other processes to make data mining accurate and quick, thus reducing the cost of completing a process. By reducing the number of clicks a user makes, large time savings can be […]

Identify and split Camel Text

Camel Text (CamelText) is best described as text where words that should be separated normally by a space have been pushed together and in every day data analysis need to be reconstructed. This only applies to text where a capital letter was used for each word such as in a title or a persons name […]

Allow or disallow numbers or text in a textbox

Userforms can be very useful and powerful when it comes to creating Data entry forms or Databases in Excel. One of the most important aspects of a database is to have clean data, that is well structured. Using this VBA example can limit the entries that a user can make in entering data into specific […]

Find specific words in a range of cells

This function is possibly something that should be built into Excel as standard, but even as Excel 2016 has just been released it’s not there. There are times when doing a CTRL+F to find specific words or a string of text in your worksheet will suffice, but there may be times when you want to […]

Check if the first character in a cell is a number

Address and Location analysis often requires the identification of the value in a cell starting with a number (ie house number) or not a number (in the case of a Flat or named house/building for instance). Using the IsNumeric or Not IsNumeric functions of VBA can help you achieve the analysis of a huge set […]

Export each worksheet to an individual XLS file

The previous blog entry relates to the same project, but this refers to the creation of the files as XLS files as opposed to the PDF files. The example code below is designed to take the activeworkbook, and create an XLS file of each and every worksheet with the filename taken from the tab name […]

Export each worksheet to an individual PDF

XLS to PDF

Datanology recently completed a project where the clients Excel workbook was used to auto create and store invoices as a new tab in the workbook, with the tab name determined by the invoice number. As the business grew, so did the number of invoices that were stored in the workbook which made it quite difficult […]

Colour alternative rows in a worksheet

Excel ‘Tables’ are a good example of clearly laid out and visible data from a database in Excel. However there may be situations when you don’t want to use a table for your data and prefer or need to use a standard worksheet to hold lots of data. Tables will automatically create every other row […]

Split Persons First name and Surname

Occasions when a persons first name and surname appear in the same cell in Excel may call for a need to ‘split’ the data apart in order to store in a more structured way, in 2 separate columns. Excel can accomplish this quite easily with the Split function in VBA, but you must also be […]

Import csv file and export in batches of 1000

This recent project assigned to datanology’s VBA team required a regular import of a standard csv file that was created from a web extract which then needed to be split into separate csv files (with headers) with a maximum of 1,000 records or rows. There was also a requirement to alter the order of the […]

Save a chart as a graphic file

Charts in Excel have become more and more functional and powerful since Excel began and now with Excel 2016 it seems incredible how innovative we can be with charts and mapping data onto Bing Maps. This quick description on how to export a chart from Excel as a .png file can be extremely valuable when […]

Copy and rename an Excel Worksheet

There are a number of methods aside from ‘Right clicking’ a tab and choosing move or copy in order to copy a worksheet in Excel. Using VBA can be a very quick way of completing this task especially if you have multiple worksheets to copy or need multiple copies added. You can even specify whereabouts […]

Adding a search function to a UserForm

We will need to know where the data we want to search is held, in this example it’s in the tab named ‘OrderFormData’ in columns B and C. Because INSTR is case sensitive, the way to be sure we don’t miss any text capitalisation changes in the data, we convert each to ‘virtual’ upper case. […]

Delete all the shapes on a worksheet

We have been faced with projects / situations where multiple pages have become clogged up with numerous and usually superfluous or duplicated items such as shapes, macro buttons etc on a worksheet. These shapes can take up memory and often get in the way of the worksheet in unpredictable ways. We have also seen copy […]

Userform TabIndex

When you have designed a userform with multiple controls such as textboxes, the user will probably want to navigate through in a logical order. If you’ve got hundreds of these controls, it is a laborious task to click on them and assign an incremental tabindex value from 1 to the end. If your controls are […]

Resize Userform for Mac or Windows

When it comes to compatibility between Mac and Windows Userforms in Excel (as long as your version of Mac Excel is 2011 or newer) there are a number of anomalies that exist and require a little bit of creative programming in VBA but one issue that we are always asked about when developing for a […]

Collection of VBA scripts

This collection of VBA scripts has been created throughout the projects created by Datanology. Whilst we re-use and find further uses for them, we’ll add them to this blog, but for now they’re fully searchable, and we will continue to add to it as often as possible. Useful VBA Copy worksheet XLSW and check if […]

VBA Delete files

Deleting files on your drive with VBA is relatively easy. The ‘kill’ command is used but be careful, as the file is not sent to the Recycle Bin, it’s deleted forever. This example code will delete the file named datanology.xls in My Documents but you can change as you require, add it into a loop […]

VBA Delete blank rows

Finding yourself in a position to clean up a worksheet is quite common, and Excel users will sometimes want to remove rows of data that fit a certain criteria rather than just use the filters. It’s a lot cleaner to remove rogue or non-required rows from your data and good practice to do it with […]

Splitting and Moving data with VBA

Splitting data can be a cumbersome and highly manual task, and could result in inaccurate data. Splitting data, moving it about and intelligently preparing large amounts of extracted data can be accomplished in seconds using a datanology VBA Solution. In the short example below (data sanitised under DPA) the extracted data which was originally 12,000 […]

Hiding and Unhiding data in Excel

When you are dealing with worksheets with a user-function – ie a user needs to complete a questionnaire or needs to add details to specific parts, to make it easier to use and have a professional appearance it may be useful to dynamically hide or unhide rows and sections of data that become applicable during […]

VBA Permutations of data string

Power sellers on Amazon and ebay will especially find this VBA solution useful to help create the multiple descriptions required to mix and jumble up their wordings and ultimately increase the SEO of their site and products. This particular code takes all the keywords in a matrix on the activesheet that are in cells A1:E6 […]

VBA Send an email

Sending an email using VBA. It is imperative that you are using Excel 2010 or newer on a Windows PC, and also use Outlook as your email software for this to work. Sub Send_email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject(“Outlook.Application”) Set OutMail = OutApp.CreateItem(0) strbody […]

Excel VBA – Create a PDF and email it

Before deciding to create a PDF from your worksheet and emailing it, it should be noted that this code is specifically for Windows versions of Excel 2007 onwards and only when Outlook is your email client. It consists of 2 pieces of VBA code although one is more commonly described as a UDF (User Defined […]

Excel VBA – List files in a folder

Excel can be used for many tasks outside it’s mainstream use as a database, calculations, data manipulation etc. It can also be used for a large amount of general tasks that maybe you wouldn’t normally think to use Excel for. This example is useful in Excel projects where you need to know the number of […]

Excel VBA Delete all blank rows

This VBA snippet is very useful to have, and in most projects, it is common for us to have this code to hand, and adapt as required to carry out a specific task on a dataset. There may be situations where your large dataset has been cleaned as far as possible, but there are still […]

Excel VBA Clear all controls on a Userform

Creating userforms in Excel to carry out basic or even complex tasks is relatively straightforward. A bit of creativity and inspiration is required as well as a little bit of Excel VBA. Datanology build userforms into the majority of our projects and they are a vital ingredient in any Excel based database system you might […]

VBA Sort a range A-Z

Sorting a range of text from A-Z or Z-A is relatively simple in Excel, whcihever version you use. Select the range and click the A-Z icon to sort in the ribbon and follow any instructions. However there may be times when you want to carry out this task in the middle of a VBA process […]

Populate a userforms combobox with unique values

Populating a ComboBox with unique values in Excel can be very useful when it comes to database building, or where a ComboBox has multiple values and it would be easier to populate the values from a range of cells that could be dynamic. This can be done in a number of ways, using advanced filter, […]

Excel VBA Auto Fill formula

Using VBA in Excel can greatly increase speed and productivity of the majority of Excel projects and systems. Even in VBA, shortcuts and usual Excel ‘quick wins’ can be used to your advantage whether that be the increased speed of doing a task, or just making something easier to do. The auto fill functions of […]

Excel Multiple Child SKUs

Our clients product list was provided to them in Excel format but each product had a Child SKU labelled across the row, sometimes up to 200 individual SKU codes. This data needed to be structured differently in order to be updated to the clients website correctly and to do this job manually would have taken […]

the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group