Nick's picture

Nick's Day By Day Excel Experts Blog

Nick's picture

XLA routines: EE_AddCalculatedColumn

EE_AddCalculatedColumn is a hugely useful sub routine that adds a calculated column to your data set, and names it. - this saves writing the VBA to add a calculated column from your data set. Uses EE_GetLastPopulatedCell to find the last cell of the data
Sub EE_AddCalculatedColumn(rngColumn As range, strFormula As String, strNewHeading As String)
    Dim rng As range
    With rngColumn
        Set rng = range(.Cells(2), .Cells(EE_GetLast
Nick's picture

XLA routines: EE_CloseOtherWorkbooks

Sub routine that closes all other workbooks than the one specified
Sub EE_CloseOtherWorkbooks(wbKeepOpen As Workbook)
    Dim wbk As Workbook
    For Each wbk In Application.Workbooks
        If wbk.Name <> wbKeepOpen.Name Then
            wbk.Close False
        End If
    Next wbk
    Set wbk = Nothing
End Sub
Nick's picture

XLA routines: EE_FileNameFromFilePath

Returns the file name from the file path
Function EE_FileNameFromFilePath(strFilePath As String) As String

    EE_FileNameFromFilePath = Mid(strFilePath, InStrRev(strFilePath, Application.PathSeparator) + 1)
End Function
Nick's picture

XLA routines: EE_HeadersCorrect

Often others deliver files to an application that need to have fixed col headers. Unfortunately, they often change them without telling you.
Nick's picture

XLA routines: EE_ApplyPivotDataFormatting

Takes a pivot table, and formats the pivot field based on a 2 col range
Sub EE_ApplyPivotDataFormatting(pt As PivotTable, FormattingRange As range)
'- takes a 2 col range, and applies the formatting specified in col 2 to the Data fields.
    Dim rngCell As range
    If FormattingRange.Rows.Count <> 2 Then Exit Sub
    For Each rngCell In FormattingRange.Rows
        pt.PivotFields(rngCell.Cells(, 1).value).NumberFormat = rngCell.Cells(, 2).value
    Next rngCell
    Set rngCell = Nothing
End Sub
Nick's picture

XLA routines: EE_SortTable2003Comp

Sort data using VBA in Excel.
Nick's picture

XLA routines: EE_GetLastPopulatedCell

Finds the last populated cell on a worksheet
Function EE_GetLastPopulatedCell(wks As Worksheet) As range
'-          Works how specialcells (lastCell)  SHOULD work
'-          Returns single cell range
    Dim lngCol      As Long
    Dim lngMaxRow   As Long
    Dim lngRow      As Long
    Dim lngMaxCol   As Long
    For lngCol = 1 To wks.UsedRange.SpecialCells(xlCellTypeLastCell).Column 'wks.UsedRange.Columns.Count
        lngMaxRow = Application.WorksheetFunction.Max(lngMaxRow, wks.Cells(Rows.Count, lngCol)
Nick's picture

XLA routines: EE_Table

If you use Excel as a database, EE_Table will return the range object of your data.
Nick's picture

XLA routines: EE_DeleteTempSheets

EE_DeleteTempSheets is used when your routine creates temp sheets that need to be deleted at the end of the routine.
Nick's picture

XLA routines: EE_RearrangeSheetsAlphabetic

EE_RearrangeSheetsAlphabetic is useful to use at the end of your procedure when you have created sheets all over the place and want them rearraged alphabetically.
Nick's picture

XLA routines: EE_RearrangeSheets

EE_RearrangeSheets is a good routine to use at the end of your procedure when you have created sheets all over the place.
Nick's picture

XLA Routines: EE_ReplaceSheet

EE_ReplaceSheet is a useful routine that replaces a sheet, and does not error if the sheet does not exist meaning you can use the routine to create a new sheet.
Function EE_ReplaceSheet(strSheet As String) As Worksheet
    Dim wksNew              As Worksheet
    Dim wbk                 As Workbook
    Dim blnDisplayAlerts    As Boolean
    Set wbk = ActiveWorkbook
    On Error Resume Next
        Set wksNew = wbk.Worksheets.Add(after:=wbk.Worksheets(strSheet))
    Err.Clear: On Error GoTo 0: On Error GoTo -1
    If wksNew Is Nothing Then
        Set wksNe
Nick's picture

XLA Routines: EE_DeleteSheets

Following on from EE_DeleteSheet, here's a routine that deletes multiple sheets
Sub EE_DeleteSheets(ArrayOrRange)
    Dim blnDisplayAlerts    As Boolean
    Dim arr
    Dim wbk                 As Workbook
    Set wbk = ActiveWorkbook
    Select Case TypeName(ArrayOrRange)
        Case "Variant()", "String"
            arr = ArrayOrRange
        Case "Range"
            arr = Application.Transpose(ArrayOrRange.SpecialCells(xlCellTypeConstants))
Nick's picture

XLA routines: EE_DeleteSheet

Deleting a sheet using VBA is more work than it should be.
Nick's picture

XLA routines: EE_End

At the end of your code, it's good practise to turn events back on, enable alerts, and put calculation back to where it was. Here's the code to turn them on:
Public lngCalc As Long
Sub EE_End()
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = lngCalc
End Sub
See here to turn them off at the start of your code: EE_Start
Nick's picture

XLA routines: EE_Start

At the start of your code, it's good practise to turn off events, disable alerts, and put calculation on manual. At the end of your routine, turn them back on. Here's the code to turn them off:
Public lngCalc As Long
Sub EE_Start()

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    lngCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
End Sub
See here to turn them back on: EE_End

Create Database in excel and ms access

Hi Team,

Please help me. I am working in vba last two years and now i got some development tool for my friend. He asked me to create employee database application in excel. so i decide to create in excel user form and database with ms access.

can store huge data in msaccess and excel form will be reliable. help me.

Venkat R

how to make expired date

i want to appear expire date in day.., question is "sheet2=mly or qly or sly or yly, sheet3=act_date, sheet4=expired date" i need to appear if sheet2=mly then add 30 days, and appear how many days remaining to expired in sheet4 automatically from today, and if sheet2=qly + 90 days, and appear how many days remaining to expired like that,,, and sly=180 days and yly=360 days, so how can i make this report,,,,

Nick's picture

Stamp Duty Changes From Autumn Budget 2014

Stamp Duty Changes

Help needed for expenses analysis

Hi All

I need to analyse the expenses month over month as attached in the sheet.Please let me know some ways in excel available



Automating transfer of seledted data from one sheet to another in excel 2003

I have many rows of data in sheet 2 of the workbook which is made up of 4 columns of text and numerical data.
What I would like is for the operator to select a record using a drop-down menu in a cell and all of the data appertaing to that record be copied onto sheet 1, is this possible?

Blank workbook problem in Excel 2007

My blank workbook is now so huge when I open it up. What happened?...and how do I fix it?

NOT so Xcellent...

have gotten very frustrated with a workbook/page issue in what i call a WOTC (Waste Of Time Challenge )... so here i go...
on the left column i have 60 different names with 10 more columns named differently, say A to J labeled from date 'net'. then there are 12 pages total to set up the workbook. then what i want to be able to do is to pull out one of the 60 names and 'stack' that row from each of the 12 pages to make a page of summary for that row name... so to say, an individual worksheet for that line...

to WOTC or not to WOTC...

that is my question...

Excel Problem

Good afternoon,

VBA: Creating function to find last row of the sheet.

Hello Experts.
Can somebody help me with the following query... This code will find last row of a sheet..but do not want to repeat for every sheet.

Dim lrow as long
Lrow=range ("a"& rows.count).end (xlup).row

Please help me to create function which can be used for any sheet..Thanking you in Advance.

Need VBA help for excel

Hi, could anybody please write a vba code for creating text format?
ex: the cell should be of 11 digits which first 4 digits should be alphabet followed by 0 and rest of the digits are numeric (SBIN0123456). If anything typed wrong should be alerted with error message.

Nick's picture

Impact Of Labour's Mansion Tax


Mansion Tax Analysis in Excel - Labour's nationalisation of London homes


Today, we'll use Excel to look at the impact of Labour's Proposal for Mansion Tax for properties over 2 million,and demonstrate some interesting findings.

The Labour party is proposing a tax on properties over 2 million. They label this a "Mansion Tax". The idea is that wealth is redistributed from the "Mansion" owners, and used to fund the government's overspending.

Calculate a list of Averages from a Timesheet

Hi Guys

I want formulas that will calculate a summary of averages from the timesheet attached(actual spreadsheet has over 50,000 rows with 12 employee). My aim is to be able to see, when I expand each subtotal/month, the average hours worked per day/week/month for the month/subtotal and in the same manner as the number of counts for all employees - summarised below the table (highlighted yellow).

In other words, each subtotal will have its summary of averages below the table when I collapse it. Cells without data should be ignored in the formula

Many thanks

Freeze first tab in first position

I know that Excel doesn't allow you to freeze one tab in the first position (i.e. making sure that a user cannot insert/move another sheet to the first position). Are you able to offer any suggestions on some VBA code that will do this for me please? I'm stuck!

Syndicate content