Determining the last row actually containing information in an Excel worksheet can be an annoying task, if you are relying on the Excel API to do the trick for you. Especially when you are not exatcly in control over the handed files you are dealing with and people are allowed to enter information anywhere it is not as easy as it should be.

Source Code automating Excel search function

Below function is determining the last row containing data in provided worksheet using the Excel search function.
This approach works only, if no cells in given sheet are merged.
Therefore merged cells are removed from a handed workbook before determining its last row containing data.

Last Updated: 19 August 2015

Hits: 4177

Recently a collegue had to import a SharePoint list into Excel for automated processing.

Below you can find the source code that I provided him with as an example.

Source Code

Sub LinkedSharePointList()
 
    ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
 
        Source:=Array(<"URL String">, <"ListNameString">, _
 
        <"{GUIDString}">), LinkSource:=True, Destination:=Range("A1")
 
End Sub

Last Updated: 25 July 2015

Hits: 4015

This function copies used range of handed worksheet and replaces it with values only.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Copy / Pasting contents of handed Worksheets UsedRange
' as values into the same range.
' @Param trg - a Worksheet object holding data.
' @Remarks -
' UsedRange of worksheet will be overwritten by itself pasting only values and nothing else.
' Formulas will be lost.
Public Function copyPasteWorksheetValues(ByRef trg As Worksheet)
    trg.UsedRange.Copy
    trg.UsedRange.PasteSpecial xlPasteValues
End Function

Last Updated: 25 July 2015

Hits: 4139