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

  • How To Find the Last Row That Contain Data in Excel?

    UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

  • Number of Cells/Rows/Columns With Formula

    A few weeks ago I received this question from someone called LS: Hi I want to write a macro that counts the following - Number of cells with formula - Number of Rows with formula - Number of Columns with formula Please let me know if there is a way of doing it in Excel VBA To find out the number of...

  • Auto Expanding VLOOKUP Table Array

    Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data. As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:C15 column no 2 is like this: =VLOOKUP(A1,B1:C15,2) With parameters such as the value we looking...

  • How To Find Row Position of a Particular Text

    Sometimes in Excel, we need to find the row position of a particular text, maybe to be able to paste certain of data right below the corresponding text or else. Very useful if we have an Excel Worksheet Template that we use a lot, with fix header on it.' Input param: Text we want to look...

  • Finding Cell with Minimum/Maximum Value in Active Worksheet

    Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells. The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: excel vba find last column, excel vba last column, last column excel vba, vba excel last column, vba last column, excel last column with data, last column in excel, excel last cell in column with value, excel macro last column name, excel vba find number of columns in an array, excel find last column, last column worksheet vba, LastColumn = ActiveSheet UsedRange Column - 1 ActiveSheet UsedRange Columns Count, look for data but return column number excel vba, LAST COLUMN EXCEL EXAMPLE

«
»