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 […]

First Word Last Word

These 2 UDF’s will extract the first and last words using a formula in your worksheet. It is worth noting that during a data split such as this, the data you are working with may require a Trim() applied to it so any rogue trailing or leading spaces are removed, making the first word and […]

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 […]

Identify if cell is coloured by conditional formatting in VBA

Identifying whether a cell is coloured by Conditional formatting. This code will only work with Excel 2010 onwards and by choosing a cell then running the short code below, a message box will output the Conditional Format Cell Fill Colour. It can be adapted to show font colour etc. Please note that Excel uses the […]

Import csv files and adapt the data with VBA

Application.ScreenUpdating = False Worksheets(“Data”).Select Range(“A2:Z25000”).ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ “TEXT;” & GetFile, Destination:=Range( _ “$A$1”)) .Name = “lget_csv” .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 65001 .TextFileStartRow = […]

UDF Choose csv file from dialog box

This UDF opens up the dialog box to choose a file to import into an Excel woorkbook. Function GetFile() As String Dim filename__path As Variant filename__path = Application.GetOpenFilename(FileFilter:=”Csv (*.CSV), *.CSV”, Title:=”Select File To Be Opened”) If filename__path = False Then Exit Function GetFile = filename__path End Function

PDF several ranges and email together

This is a newer and more efficient way of creating a PDF from a specific range on a worksheet, saving it and emailing it out. ** It’s important to note that you can’t select ranges from different worksheets with this code. All ranges need to be on the same worksheet. If you have different structures […]

PDF and email together

This is a newer and more efficient way of creating a PDF, saving it and emailing it out. Sub pdf_and_email() FName = “[email protected]” DDate = Format(Now(), “mm-dd-yyyy-hh-mm-ss”) Subj = “Subject of email here” ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ ThisWorkbook.Path & “\” & FName & “.pdf”, Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False ‘Send the PDF out here […]

Remove duplicates on a Mac (Last occurrence is kept)

Removing duplicates on a Mac is slightly different to on a PC version of Excel. This is more brute force than elegant and may take a little longer, but it does work. ‘Code designed 25/2/2017 by datanology.co.uk Application.ScreenUpdating = False ‘Find last row With ActiveSheet lastrow = .Cells(.Rows.Count, “B”).End(xlUp).Row End With For d = lastrow […]

UDF iso Week Number

Easily identify the iso weeknumber with a formula. Paste this UDF into a module in the VB Editor (ALT+F11) then use the following formula on a worksheet. =isoweeknumber(A1) This will return the iso week number that cell A1 falls under – ie 01/03/2017 = week 9 Public Function IsoWeekNumber(d1 As Date) As Integer Dim d2 […]

Get the current cells column letter

A formula to return the column letter(s) when placed in any cell. For instance if the formula was put in cell G56 the result in cell G56 would be G. If it was put in IMB213 then the result would be IMB. =LEFT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),”$”,””),LEN(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),”$”,””))-LEN(ROW()))

Add every other cell on a row

Occasionally when you have a large number of cells in a row that you want to sum, ideally it would just be in a range. ie A1:A7500 for instance. This would sum up everything from Cell A1 to Cell A7500. Now what if the data contained daily takings where 1 column was Gross and the […]

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

Excel copy a text file into a backup folder

Copy a text file into a backup folder, delete the original, and add todays date and time to the backup filename. Dim FileUser As String Dim FileuserDocFILE As String Dim output As String ‘Move the updated file Dim d As String, ext, x Dim srcPath As String, destPath As String, srcFile As String srcPath = […]

Send excel row to text file where large amount of columns used

This example sends a large amount of data from a row into a text file in the same path as the excel file is saved. With thousands of columns per row available, it would take a long time and be open to errors to assign each cell on a row, set the delimit character between […]

Recreate each worksheet as values instead of formulas

‘Recreate each worksheet as values instead of formulas and scroll to top / A1 For Each ws In Worksheets ws.Activate ws.Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues ws.Range(“a1”).Select Application.Goto Reference:=ws.Range(“a1”), Scroll:=True Next ws This VBA can be applied to any workbook where you want the formulas to be copied and pasted as values – ie as text in […]

Create new worksheet for each unique value in a list

This quick script will go down column A of a tab named ‘Data’ and for each unique value it finds, it will create a worksheet with that name and then for each occurrence of the value in the list it will populate the new sheet line by line with the data alongside the value in […]

Count if number is on any other sheet

With ActiveSheet lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row End With OTP = 2 For n = 1 To lastrow srce = ActiveSheet.Range(“A” & n) For x = 1 To 25 If srce = Worksheets(“Control”).Range(“A” & x) Then nme1 = ActiveSheet.Cells(n, Worksheets(“Control”).Range(“B” & x)) nme2 = ActiveSheet.Cells(n, Worksheets(“Control”).Range(“C” & x)) add1 = ActiveSheet.Cells(n, Worksheets(“Control”).Range(“D” & x)) add2 = […]

Count Months in a Pivot List (Formula)

=COUNTIF(A:A,”Jan”)+COUNTIF(A:A,”Feb”)+COUNTIF(A:A,”Mar”)+COUNTIF(A:A,”Apr”)+COUNTIF(A:A,”May”)+COUNTIF(A:A,”Jun”)+COUNTIF(A:A,”Jul”)+COUNTIF(A:A,”Aug”)+COUNTIF(A:A,”FSep”)+COUNTIF(A:A,”Oct”)+COUNTIF(A:A,”Nov”)+COUNTIF(A:A,”Dec”) This formula counts all the occurrences of a month in the list when using multiple years and you need to know how many months it covers.

Excel VBA to HTML page

‘HTML STUFF*********************************************** a = ” ~!DOCTYPE HTML PUBLIC “”-//W3C//DTD HTML 4.01 Transitional//EN”” “”http://www.w3.org/TR/html4/loose.dtd””>” b = “~!– Original design work, including graphics and all related scripts, Copyright (c) OCDesignsOnline.com, All Rights Reserved. Used with permission by Messina-Hembry-Clothing. –>” c = “~html>~head>” d = “~meta name=””viewport”” content=””width=device-width, initial-scale=1″”>” e = “~link href=””http://ebay.sunandfuninoc.com/global/ebayglobal-res.css”” rel=””stylesheet”” type=””text/css””>” f = “~link […]

Project: Split Countries into new file

The project brief was to create a macro on a button that would follow this process: Open dialog box so user can choose file to work with Identify which column is headed ‘Country’ Take each ‘Country’ by its value (UK, ES, USA etc) in the column and split them into their own file saved in […]

Richard Specification Compliance

Sub Add_matrix_to_Richards_Specification_sheets ‘Adding the Specification Compliance scoring system for Richard ‘Each sheet ending in Specification Compliance needs this applied ‘but may be different sizes, numbers of rows ‘Loop through all worksheets, and if name ends in Specification Compliance then run the loop Dim WS_Count As Integer Dim I As Integer WS_Count = ActiveWorkbook.Worksheets.Count For I […]

VBA border around a cell

Border all around a cell Range(“K5”).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 […]

Fill multiple rows with printing costs

Zest Print Prices Sub Button1_Click() ‘Get lastrow of the Template form With Worksheets(“Template”) lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row End With With Worksheets(“££ Print Prices 2016”) lastrowPP = .Cells(.Rows.Count, “A”).End(xlUp).Row End With ‘Now we know the last row, go down each row and get the Item code, Print Method and max colours For r = 2 To […]

TPS Checker

Application.ScreenUpdating = False ActiveSheet.Cells.Interior.Color = xlNone x = 4 Z = 3 For n = 1 To 2 Sheets(“TPS_IMPORT”).Select With ActiveSheet.QueryTables.Add(Connection:=”TEXT;F:\” & n & “.txt”, Destination:= _ Range(“$A$1”)) .Name = “1” .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True […]

Disable Cut, Copy, Paste in Excel

This code will disable the function of copy, cut and paste in an Excel workbook, rendering the file very difficult to remove data from. There are other functions that can be restricted in Excel if you need even more complex protection from data theft such as rendering the right click button disabled or connecting hidden […]

Big Brand Clothing Code

Logic will be to make them all disappear so create a Call All_Gone sub routine. Sub All_Gone Label46.visible = false Label57.visible = false Label59.visible = false Label56.visible = false Label74.visible = false Label51.visible = false Label60.visible = false Label53.visible = false Label57.visible = false Label75.visible = false Label49.visible = false Label50.visible = false Label55.visible = […]

TabIndex

This series of blogs created by datanology are updated as and when new features, problems & solutions or general tips regarding a particular function in Excel is experienced in our projects. The aim is to provide a very short title but to cover every aspect that we can with it. Where code or formulas can […]

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 […]

Collection of COM port communications in Excel

http://dev.emcelettronica.com/serial-port-communication-in-excel-vba Option Explicit ‘——————————————————————————- ‘ ‘ This VB module is a collection of routines to perform serial port I/O without ‘ using the Microsoft Comm Control component. This module uses the Windows API ‘ to perform the overlapped I/O operations necessary for serial communications. ‘ ‘ The routine can handle up to 4 serial ports […]

Remove email addresses from customer database on unsubscribe

We have recently completed several projects where our client has a large customer database, names and email addresses in one list, and in another separate list is a download of all the email addresses that have chosen to unsubscribe or requested no further contact via email. A company’s reputation is at risk if they continue […]

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 […]

Akira repairs

On the exported spreadsheets, the formula needs to be reinstated, and the rest copied and pasted as values. VAT is still feeding through even though No is chosen Creating commission invoice, putting date as 2818 – column G Agreement dropdown back to ‘Lease’ Introducer and Supplier systems – Stop clearing the form when it adds […]

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 […]

Export all worksheets to individual PDFs

In a recent project undertaken by Datanology, a workbook had become large enough to be cumbersome in opening, saving, closing and general navigation. Over 1,500 worksheets of generated invoices were stored in the workbook and this was growing at a rate of around 10 per day. A solution was required to deal with this growth […]

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 […]

UDF Get the comments from a cell

Comments in Excel can be stored in a cell and their locations are identified by a small red triangle in the top right of a cell. These comments can contain lots of text, carriage returns etc and can be very useful for a user in their own spreadsheet or a user wishing to explain specific […]

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 […]

Town to County

As part of Datanology’s recent projects, our UK Postcode analysis can validate any given UK Postcode by it’s structure, split the UK Postcode into it’s first part – OUTER, and then using our comprehensive lookup databases can determine the town/city and then the County for each given postcode. This allows us to build a standardised […]

Postcode to Town and City

This database created by Datanology contains the reference data to lookup the town/city related to the OUTER Postcode in the UK. To use Excel to identify the OUTER Postcode from a full UK Postcode, see here, or if you need to validate the structure of a UK Postcode, we have created a UDF to Validate […]

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 […]

Force #N/A error in cell

This may seem illogical, and we even asked ourselves why anybody would want to do this, when the whole point of a clean and fully functional excel project is to avoid errors and ensure nothing can get in the way of an accurate result or outcome. However, we were asked to create a formula that […]

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 […]

Data Mining etc

Data Capture

There are many different ways of describing ‘Data Mining’ and probably lots of different understanding of what it means. At the time of writing there is no official or standardised description of what it is. Datanology carry out a number of different processes, and several of these could be described as ‘Data Mining’. The art […]

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 […]

Motorway Junction Geocoding

In location analysis for Road Traffic Accidents for instance, or even just general mapping the Geocodes for less specific road areas, ie those without an identifiable postcode are extremely important. Whilst this blog contains just a list of all the UK Motorway Junction Geocodes, it is searchable and you may copy and paste it into […]

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 Choose a file

There may be many situations in your VBA project when you might need to open up the dialog box and choose a file to carry out a task with. This simple and quick solution will do just that, open up the dialog box at default C: and allow you to choose a file (fname). You […]

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 […]

Datanology and Cheetham Hill Squash

Datanology are delighted to support CHCC Squash teams for the 2015/16 season – Good luck ! “Data can help us make smarter decisions but only if you can interpret it quickly, accurately and with confidence.” Datanology are experts in the field of Excel automation and data management. We have built projects for small, medium and […]

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 […]

the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group