FIND THE LAST COLUMN IN A WORKSHEET USING VBA

Use the following code to find the last data column in a worksheet.

Get Last Data Column –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

'Method Overview '*********************************************************************** 'name: GetLastDataColumn 'params: Worksheet 'returns: none 'created: 01/04/2013 'reviewed: 'comments: To calculate the last data column of a worksheet '*********************************************************************** Public Function GetLastDataColumn( _ ByVal TargetSheet As Worksheet _ ) As Long On Error Resume Next GetLastDataColumn = TargetSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column End Function

Usage –

1 2 3 4 5 6 7 8 9

Sub Test() Dim lngLastCol As Long lngLastCol = GetLastDataColumn(Sheet1) 'In this line, Sheet1 is a worksheet codename 'or lngLastCol = GetLastDataColumn(ThisWorkbook.Sheets("Sheet1")) 'In this line, Sheet1 is a worksheet display name End Sub

You can also use integer type to catch the return value from a function until and unless the return value does not exceed the limit of integer.

Please share your thoughts on this post in the comments section.

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.