Category Archives: Uncategorised

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

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

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

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

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

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

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

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

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

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

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

Excel Column Letters to Numbers

When dealing with large databases and datasets in Excel, it is often handy to know the numbers that relate to the column letters. This is especially useful when you are using the .cells rather than .range in your VBA projects. Columns in Excel are numbered in the same fashion as rows – starting at 1 […]

the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group