Get Position of Last Column Containing Data with Excel VBA
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only this time, we change the row into column.
Function LastColumn() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = ix
End Function
The function above will return the appropriate position of the column even when the last column containing data in a hidden state.
The results of this function is the column number, not the column name (1 for A, 2 for B, etc). If what we need is the column name, then we can use the excel column number to column name converter that I wrote before.
Related Entries
External Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
Tagged with: excel vba, vba column, vba get last column, vba tutorial
hwsris
Thank ,
Your code make my idea for find amount row of worksheet.
Normally I use
Range("A1", Range("A10000").End(xlUp)).Select
for my work.
Excel VBA Macro
my pleasure hwsris, thanks for coming back