VBA – Tips & Tricks

VBA – Retrieve Last Row From More Than One Column

Normally, it’s enough to know the last row in a specific column. In those cases I normally just use this widely method:

'Getting last row from column A
LastRow = Range("A" & Rows.Count).End(xlUp).row

But when you have several columns (for example 1 to 10) and you need to retrieve the last cell in use in any of these columns, we need something else. A big thanks to Jan Brun Rasmussen for having provided the following brilliant solution:

Sub DefineRange()

Dim StartColumn As Integer
Dim EndColumn As Integer
Dim LastRow

'define first and last column of the range
StartColumn = 2
EndColumn = 10

LastRow = FindLastRow(StartColumn, EndColumn) 'call the function FindLastRow and return the value to the variable LastRow
MsgBox "Last row is " & LastRow 'show and tell
End Sub

'---the above sub calls the function below, providing the function with the range parameters. 

Function FindLastRow(iColI As Integer, iColN As Integer) As Long
    Dim iRowN As Long
    Dim iRowI As Long
    'Loop thorugh the columns
    For i = iColI To iColN
        'Define each columns' last row
        iRowI = Cells(Rows.Count, i).End(xlUp).Row
        'if last row is larger than in the previous column, save row number to iRowI
        If iRowI > iRowN Then iRowN = iRowI
    Next i
    FindLastRow = iRowN
End Function

VBA – Check Extension of File

When looping through all files in a folder, it’s often necessary to check the extension of each file, so you only mess with Excel files, for example.

The code below shows how to retrieve the extension of a file, define an array with “allowed” extensions, and match the extension of the file to the array.

Sub KørImport()

Dim MinExtensionX
Dim Arr() As Variant
Dim lngLoc As Variant

'Retrieve extension of file
MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)

Arr = Array("xls", "xlsx") 'define which extensions you want to allow
On Error Resume Next
lngLoc = Application.WorksheetFunction.Match(MinExtensionX, Arr(), 0)
If Not IsEmpty(lngLoc) Then ' 

'DO STUFF if it's an xls/xlsx file. Otherwise, chose next file in folder

End If