Home > Forum Home > Excel Project Management Template > Return last column with value Share

Return last column with value

Excel Help for Return Last Column With Value in Excel Project Management Template


Forum TopicPost Reply Login

Return Last Column With Value

Rate this:
(3/5 from 1 vote)
ConfusedI have an excel spreadsheet with column headings and row headings.  The column headings are dates while the row headings are drawing numbers.  Each time a drawing was revised an x would go under the date it was done.  There are multiple x's per row. I was looking for a formula that would produce the most recent date of each drawing.  If that is not possible a combination of the dates would also be acceptable.
 Mike Tang
 Posted by on
 
Replies - Displaying 1 to 4 of 4Order Replies By: Most Recent | Chronological | Highest Rated
Oops
Rate this:
(3/5 from 1 vote)
You will need to use a user defined function in VBA to determine the last used column location with an X value.  Once you have the column, you can apply this to the row number with the dates to get the last date either in VBA or via a formula INDIRECT.

The user defined function will take the row range such as:

VBA Code:
Function LASTCOL(rngRow As Range) As Variant 
Dim tmpRange As Range
  Dim i As Integer, cnt As Integer
  Application.Volatile
  Set tmpRange = rngRow.Rows(1).EntireRow
  Set tmpRange = Intersect(tmpRange.Parent.UsedRange, tmpRange)
  cnt = tmpRange.Count
  For i = cnt To 1 Step -1
      If Not IsEmpty(tmpRange(i)) Then
         LASTCOL = tmpRange(i).Value
        Exit Function
      End If
  Next i
End Function
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Thanks for the help, but I am still having trouble finding a function to find the column number.
 Mike Tang
 Posted by on
Shocked
Rate this:
(3/5 from 1 vote)
You can change the user defined function slightly by returning the column number instead of the value.  To do so replace LASTCOL = tmpRange(i).Value with LASTCOL = tmpRange(i).Column
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hello Friends,

I have also some problem regarding  Gantt Chart. I want to know all detail about  Gantt Chart? If anyone have knowledge than please tell me.

Thanks and Regards,
Kynthia Melissa
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Return last column with value in the