Nick's blog

Nick's picture

Nick's Day By Day Excel Experts Blog

Nick's picture

XLA Routines: EE_TableFreeHeading

Finds the next available cell to put a heading on a data table
Function EE_TableFreeHeading(sht As Worksheet) As Range
'http://excelexperts.com/xla-routines-eeTableFreeHeading    for updates on this function
' finds the next free cell to the right of a table
    
    Set EE_TableFreeHeading = sht.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
 
End Function
Nick's picture

XLA Routines: EE_ExtractRow

Extracts a row of data and transposes it - useful for analysing records with many columns
Sub EE_ExtractRow(Optional SourceSht As Worksheet, Optional TargetSht As String, Optional RowToExtract As Long, Optional wb As Workbook, Optional blnTranspose As Boolean = True)
' takes the selected cell row as default
' copies and transposes onto a new sheet
'http://excelexperts.com/xla-routines-eeExtractRow    for updates on this sub routine

    If SourceSht Is Nothing Then
        Set SourceSht = ActiveSheet
    End If
 
    If wb Is Nothing Then
        Set wb = ActiveWorkbook
Nick's picture

XLA Routines: EE_ReplaceErrors

Replaces the errors on a sheet
Sub EE_ReplaceErrors(Optional rng As Range, Optional ReplaceWith As String)
'http://excelexperts.com/xla-routines-eeReplaceErrors    for updates on this sub routine
' takes a range and replace the errors in the range

    Set rng = EE_TableDefault(rng)
    On Error Resume Next
    rng.SpecialCells(xlCellTypeFormulas, 16).value = ReplaceWith
    rng.SpecialCells(xlCellTypeConstants, 16).value = ReplaceWith
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
End Sub
Nick's picture

XLA Routines: EE_AddTableCalculatedColumn

Advanced function for adding a calculated col to a table
Sub EE_AddTableCalculatedColumn(Header As String, Formula As String)
'http://excelexperts.com/xla-routines-eeAddTableCalculatedColumn    for updates on this sub routine
' adds a calculated column to a table on the activesheet
Dim HeaderCol As Long
Dim myData  As Range
 
    If Cells(1).CurrentRegion.Rows.Count = 1 Then Exit Sub ' no data
    Set myData = EE_TableGetColumnData(Header)
    myData.ClearContents
    HeaderCol = EE_TableHeadingCol(Header)
    Cells(2, HeaderCol) = Formula
    myData.value = myData.value
Nick's picture

XLA Routines: EE_FormatCols

Useful routine for formatting columns from an imported file
Sub EE_FormatCols(rngSource As Range, Optional rngTarget As Range)
'http://excelexperts.com/xla-routines-eeFormatCols    for updates on this sub routine
' takes a source range containing the heading and format in 2 cols
' looks in the target range and formats the target range
Dim rngHd As Range
Dim rngFound As Range
 
    Call EE_Start
 
    Set rngTarget = EE_TableDefault(rngTarget).Rows(1)
    For Each rngHd In rngSource.Rows
        Set rngFound = rngTarget.Find(rngHd.Cells(1).value, , xlValues, xlWhole)
 
Nick's picture

XLA Routines: EE_ReverseSignInRange

Sub EE_ReverseSignInRange(rng As Range)
'http://excelexperts.com/xla-routines-eeReverseSignInRange    for updates on this sub routine
' takes a range as input and reverses the sign of numbers in the range
' turns positive numbers negative and negative numbers positive
Dim theCell
 
    Set rng = Intersect(rng.Parent.UsedRange, rng)
    ' rng.select
    For Each theCell In rng
        If Application.IsNumber(theCell.value) Then
            theCell.value = theCell.value * -1
        End If
    Next
 
End Sub
Nick's picture

XLA Routines: EE_ListSheetNames

Creates an index sheet with hyperlinks
Sub EE_ListSheetNames(Optional NewShtName As String)
'http://excelexperts.com/xla-routines-eeListSheetNames    for updates on this sub routine
' lists the names of the sheets on a new sheet
Dim sht As Worksheet
 
    If NewShtName = "" Then
        NewShtName = "Index"
    End If
 
    Call EE_ReplaceSheet(NewShtName)
    i = 1
 
    On Error Resume Next
    For Each sht In ActiveWorkbook.Sheets
        Cells(1).Offset(i) = sht.Name
 
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(1).Offset(i), Address:="", Sub
Nick's picture

XLA Routines: EE_PivotTurnOffNonBlank

Sub EE_PivotTurnOffNonBlank(pvtField)
'http://excelexperts.com/xla-routines-eePivotTurnOffNonBlank    for updates on this sub routine
' turns off non-blank items from pivot table
' assumes 1 pivot on page, no error checking

    Dim pvtItem
 
    pvtField.CurrentPage = "(All)"
    pvtField.EnableMultiplePageItems = True
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Name = "(blank)" Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
        End If
    Next
 
End Sub
Nick's picture

XLA Routines: EE_PositionInRange

Function EE_PositionInRange(Header As String, rng As Range) As Long
'http://excelexperts.com/xla-routines-eePositionInRange    for updates on this function
' Takes a heading name, range
' returns the position in the range as long
' returns 0 if not found
    On Error Resume Next
    EE_PositionInRange = Application.WorksheetFunction.Match(Header, rng, 0)
    If Err.Number <> 0 Then
        EE_PositionInRange = 0
    End If
 
End Function
Nick's picture

XLA Routines: EE_TableDefault

Creates a range object that represents a table of data - used to pass to other subs that require data
Function EE_TableDefault(Optional rngTable As Range) As Range
'http://excelexperts.com/xla-routines-eeEE_TableDefault    for updates on this function
' returns the currentregion around the first cell - a good guess for a table of data's range
    
    Set EE_TableDefault = rngTable
    If rngTable Is Nothing Then
        Set EE_TableDefault = ActiveSheet.Cells(1).CurrentRegion
    End If
 
End Function
Nick's picture

XLA routines: EE_PivotRemoveTotals

EE_PivotRemoveTotals is a simple routine that removes the totals from a pivot
Sub EE_PivotRemoveTotals(Optional pt As PivotTable)
'http://excelexperts.com/xla-routines-eePivotRemoveTotals  for updates on this routine
    If pt Is Nothing Then
        If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
        Set pt = ActiveSheet.PivotTables(1)
    End If
 
    pt.RowGrand = False
    pt.ColumnGrand = False
End Sub
Nick's picture

XLA routines: EE_SortCols

EE_SortCols sorts columns by a predefined range of headers...
Nick's picture

VBA Developer Jobs in the UK

Live VBA Developer Jobs in the UK

{"what":" VBA developer","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Developer Jobs in the UK

Live Excel Developer Jobs in the UK

{"what":" excel developer","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel VBA Jobs in the UK

Excel VBA Jobs in the UK

{"what":" excel vba","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel VBA Jobs in the UK

Nick's picture

Excel Jobs in Cardiff

Live Excel Jobs in Cardiff

{"what":"Excel","where":"Cardiff","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Dundee

Live Excel Jobs in Dundee

{"what":"Excel","where":"Dundee","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Perth

Live Excel Jobs in Perth 

{"what":"Excel","where":"Perth ","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Inverness

Live Excel Jobs in Inverness

{"what":"Excel","where":"Inverness","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Aberdeen

Live Excel Jobs in Aberdeen

{"what":"Excel","where":"Aberdeen","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Edinburgh

Live Excel Jobs in Edinburgh

{"what":"Excel","where":"Edinburgh","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Glasgow

Live Excel Jobs in Glasgow

{"what":"Excel","where":"Glasgow","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Belfast

Live Excel Jobs in Belfast

{"what":"Excel","where":"Belfast","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Leeds

Live Excel Jobs in Leeds

{"what":"Excel","where":"Leeds","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Bristol

Live Excel Jobs in Bristol

{"what":"Excel","where":"Bristol","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Birmingham

Live Excel Jobs in Birmingham

{"what":"Excel","where":"Birmingham","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Liverpool

Live Excel Jobs in Liverpool

{"what":"Excel","where":"Liverpool","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Manchester

Live Excel Jobs in Manchester

{"what":"Excel","where":"Manchester","results_per_page":"20","country":"gb","widget":"job-feed"}
Syndicate content