Find First or Last Populated Column in a sheet

Code:
Option Explicit

Sub Test_xlFirstLastCols()

' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet

Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"


End Sub

Function xlFirstCol(Optional WorksheetName As String) As Long

' finds the first populated col in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With

End Function

Function xlLastCol(Optional WorksheetName As String) As Long

' finds the last populated col in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With

End Function

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.


Test the code:

In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).

Regards,
Praveen KVC
December 28 2009