Home Excel Powerpoint Word Outlook Error Codes

VBA Useful Codes

Welcome to the World of VBA
Home > VBAUsefulCodes

Finding Last Column in Excel VBA

Gives information about last used column in excel

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
sht.UsedRange 'Refresh UsedRange
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub


Enter your e-mail below and get notified on the latest blog posts.


VBA Outlook KPI Excel Alt+F11 Dashboards Macros Recording Graphs Automation Developer WaterFall Powerpoint Charts Pivot Tables Forecast Charts

Follow Us