?
Solved

identifying last value in a row.

Posted on 2011-10-07
8
Medium Priority
?
175 Views
Last Modified: 2012-08-14
I have a VBA app (Excel), in which I need to identy the column notation (A, B,...) of the last value in a row. (I need to then insert that value in a subsiquent formula.) I already know which row to test on.

I would go to specific row and then determione the column notation value (lacall).
Then I would do a copy&paste  operation as such:

    SourceSh.Range(lacall & (958 + ioffset) & "." & lacall & (963 + ioffset)).Copy
                        r.Offset(0, 63).PasteSpecial xlPasteValues, , , True

Curently I retrieve LACALL from the user's workbook entry.  Would be better If I figure it out.

Thanks
Don OHara
0
Comment
Question by:donohara1
  • 4
  • 2
  • 2
8 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36933826
This function will get the column letter for the last cell in the row you pass to it:
Public Function GetLastColLetter(ByRef rngRow As Excel.Range) As String

    Dim c As Excel.Range
    Dim strRange As String
    
    Set c = rngRow.EntireRow.Cells(1, rngRow.Worksheet.Columns.Count)
    Set c = c.End(xlToLeft)
    
    strRange = c.Address(False, False)
    GetLastColLetter = Replace(strRange, CStr(c.Row), "")
    
End Function

Open in new window

0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36933844
Is the range operator in your location '.'? - if not you may need to change it to a ':' in the example copy you give.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36934110
@andrewssd3 - is there a partcular reason you declare as Excel.Range as opposed to just Range?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36935369
Yes - it's really just a habit, but I have done a lot of developing of cross-product applications, where I had references say to Word and Excel in the same project.  Then it's sometimes essential to differentiate - Range would be one of those, as both Word and Excel have Range objects.  I just tend to do it all the time now, as it saves trouble later if the code need to be used in a cross-product context.
0
 

Author Comment

by:donohara1
ID: 37034081
DEMO Code

Dim THisRow As Range
Dim GetLastColLetter As String
  ...
Set Sourcewb = Workbooks.Open(filepath & Filename, False)      (This works - DOH)
   ...  
 Set SourceSh = Sourcewb.Sheets("Agg Perf History")
                       THisRow = SourceSh.Range("a61:aaa61")
                       lacall = GetLastColLetter(THisRow)
                       MsgBox "lacall is: " & lacall

------------

When I run this, I get 'Compile Error'

AS you can see, I am looking at the row a61 to ea61 and want to find the column ID for the latest value.   Is there a simple code to select this row and call the function so I can use the column ID in a number of copy&paste commands.


Thanks,
Don
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 600 total points
ID: 37034390
It would be useful for you to identify the line that the compile error occurred on - for future reference... :)

try:

set THisRow = SourceSh.Range("a61:aaa61")

Since THisRow is defined as a range object.

I assume lacall is declared as string?

Cheers,

Dave
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1400 total points
ID: 37036846
I have modified your code so it now gets the column number into the variable lacall - there were a couple of problems, mainly that you were defining GetLastColLetter as a variable, rather than using it as a function - this should do what you want:

Sub main()
Dim sourceWb As Excel.Workbook
Dim sourceSh As Excel.Worksheet
Dim thisRow As Range
Dim lacall As String
'   ...  Obviously you need to define filepath and filename - they may be parameters to this routine
Set sourceWb = Workbooks.Open(filepath & Filename, False)      '(This works - DOH)
'   ...
Set sourceSh = sourceWb.Sheets("Agg Perf History")
 
Set thisRow = sourceSh.Range("a61:aaa61")
lacall = GetLastColLetter(thisRow)
MsgBox "lacall is: " & lacall


End Sub

Public Function GetLastColLetter(ByRef rngRow As Excel.Range) As String

    Dim c As Excel.Range
    Dim strRange As String
    
    Set c = rngRow.EntireRow.Cells(1, rngRow.Worksheet.Columns.Count)
    Set c = c.End(xlToLeft)
    
    strRange = c.Address(False, False)
    GetLastColLetter = Replace(strRange, CStr(c.Row), "")
    
End Function

Open in new window

0
 

Author Closing Comment

by:donohara1
ID: 37040220
Thanks to both of you.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question