For a project I created a more detailed last row finder which looks at more than one column.
Could you provide any feed back on the code below.
Function lColCount(ws As Worksheet, Optional iWhichRow As Integer = 1) As Long
Dim sMaxCol As String
sMaxCol = Cells(iWhichRow, ws.Columns.Count).Address
lColCount = ws.Range(sMaxCol).End(xlToLeft).Column
Function`
Function lRowCount(ws As Worksheet, Optional iWhichCol As Integer = 1) As Long
Dim sMaxRow As String
sMaxRow = Cells(ws.Rows.Count, iWhichCol).Address
lRowCount = ws.Range(sMaxRow).End(xlUp).Row
End Function
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function`
Sub findLast()
Dim LC As Long
Dim LR As Long
Dim LCa As String
Dim ws As Worksheet
Dim i As Integer
Dim arr() As Variant
'Assigns ws = sheet1
Set ws = Sheet1
'Sets LC = to the last column number of default row 1
LC = lColCount(ws)
'I counter = 1
i = 1
'Defines array as 1 to the last column number
ReDim arr(1 To LC)
'Loops untill I = Last column counter + 1
Do While i <> LC + 1
'Sets Lr to last row of the looping column i
LR = lRowCount(ws, i)
'Sets last row to arr(I) value loop
arr(i) = LR
'Adds 1 to I ready for next loop
i = i + 1
Loop
'Finds maximum value in array and uses that as last row
LR = Application.WorksheetFunction.Max(arr())
'Use to check the array value in the Immediate window
Dim Res As Variant
For Each Res In arr()
Debug.Print Res
Next Res
'Col_letter function finds the letter of the column number
LCa = Col_Letter(LC)
'copies range A1 sheet1 and all data to sheet2
ws.Range("A1:" & LCa & LR).Copy Sheet2.Range("A1")
End Sub