?
Solved

Not getting the correct column count from another worksheet in another workbook

Posted on 2011-10-28
15
Medium Priority
?
245 Views
Last Modified: 2012-05-12
I found the code below online.  It was originally a subroutine and it worked fine.  I'm trying to convert it into a function that returns the column number to another calling procedure.  The sticking point is that this function being called within a spreadsheet to return the number of data columns in a spreadsheet located in another workbook.  The problem is that I am not getting back the correct column count of the worksheet located in another workbook.  I think the problem is in the for loop and how I am resolving the correct worksheet
Private Function LastRealPopulatedColumn(excelApp As Excel.Application, rng As Excel.Range) As Integer
    Dim LastR As Integer
    Dim LastC As Integer
    Dim LastRealC As Integer
    Dim counter As Integer
    
    excelApp.ActiveCell.SpecialCells(xlLastCell).Select
    LastR = excelApp.ActiveCell.Row
    LastC = excelApp.ActiveCell.Column
    
    LastRealC = 1
    'MsgBox LastC
    
    For counter = LastC To 1 Step -1
'Error occurs here
        excelApp.Range(rng.Cells(LastR, counter), rng.Cells(LastR, counter)).Select
        Selection.End(xlUp).Select
        If Not IsEmpty(excelApp.ActiveCell.Value) Then
            LastRealC = excelApp.ActiveCell.Column
            Exit For
        End If
    Next
    MsgBox LastRealC
    
     LastRealPopulatedColumn = LastRealC
End Function

Open in new window

0
Comment
Question by:chtullu135
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37047594
The select method actually selects the range. And you do not expect the selection to be jumping around when evaluating a function. So try to rewrite it by avoiding select.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37047651
Hi, chtullu135.

Are you actually running two copies of Excel or simply a single version with two files open? What is the line of code which calls the above function?

Thanks,
Brian.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37047713
This function has a strange mixture of selecting and other techniques, which probably comes from its history as a Sub.  Rather than try to fix it, here is a similar routine I wrote a while ago and have been using successfully.  It should not matter if the range you pass to it is in an external workbook (although that workbook would need to be open for you to get the reference to pass in the first place.
Public Function GetLastColNum(ByRef SourceRow As Excel.Range) As Long
'Returns the last non-empty column in the Col/s.  Used in dynamic charts.
    Dim endRange As Excel.Range
    Dim bottomRange As Excel.Range
    Dim i As Long
    Dim maxCol As Long
    Dim currCol As Long
    Dim lngCols As Long
    Dim startRow As Long, endRow As Long
    
    maxCol = 0
    lngCols = SourceRow.Worksheet.Columns.Count
'    Set endRange = SourceColumn.Worksheet.UsedRange
'    lngCols = endRange.Col + endRange.Cols.Count
    
    startRow = SourceRow.Row
    endRow = startRow + SourceRow.Rows.Count - 1
    
    For i = startRow To endRow
        Set endRange = SourceRow.Worksheet.Cells(i, lngCols)
        Set bottomRange = endRange.End(xlToLeft)
        currCol = bottomRange.Column
        If currCol > maxCol Then maxCol = currCol
    Next i
    
    GetLastColNum = maxCol
    
End Function

Open in new window

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!

 

Author Comment

by:chtullu135
ID: 37047768
Hello Brian,

Actually I am running two copies of Excel.  One for the first workbook and one for the second workbook.  I am passing the application object to the function in order to minimize the number of instances of Excel that are running
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37047851
Thanks chtullu135. Andrew's code looks interesting.

Andrew, isn't your code dependent on the row(s) you pass to it?
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 200 total points
ID: 37047993
chtullu135,

I've shamelessly stolen Andrew's excellent idea for the code below. The function simply requires a range object to be passed to it - the demo subroutine shows some options.
Sub Demo_LastRealPopulatedColumn_II()

Debug.Print LastRealPopulatedColumn_II(Workbooks("Personal.xlsb").ActiveSheet.Range("A1"))
Debug.Print LastRealPopulatedColumn_II(ActiveCell)
Debug.Print LastRealPopulatedColumn_II(ActiveSheet.UsedRange)

End Sub

Public Function LastRealPopulatedColumn_II(ByRef SourceRange As Excel.Range) As Long
' Returns the last non-empty column in the Col/s.
' See http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27421236.html
Dim LastR As Long
Dim LastC As Long
Dim LastRealC As Long
Dim counter As Long

With SourceRange.Parent      'Returns the sheet.

    LastR = .Range("A1").SpecialCells(xlLastCell).row
    LastC = .Range("A1").SpecialCells(xlLastCell).Column
    
    LastRealC = 1
     
    For counter = LastC To 1 Step -1
        If Not IsEmpty(.Cells(LastR + 1, counter).End(xlUp).Value) Then 'This will give an error if the last used row is actually the last row.
            LastRealC = counter
            Exit For
        End If
    Next

End With
    
LastRealPopulatedColumn_II = LastRealC
    
MsgBox LastRealPopulatedColumn_II

End Function

Open in new window

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37048001
Oh, I was lazy and as the comment indicates, a sheet whose last used row is actually the last row on the sheet (i.e. the sheet is full) will cause an error. If you like the rest, I can get rid of this.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37048077
Just for fun, you can run Demo_Excel_LastRealPopulatedColumn_II (below) to Word and it'll run quite happily. (Don't forget to copy in LastRealPopulatedColumn_II as well.)
Sub Demo_Excel_LastRealPopulatedColumn_II()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
    
Set xlApp = CreateObject("excel.application")
Set xlWB = xlApp.Workbooks.Open("D:\periodtable.xls")

Debug.Print LastRealPopulatedColumn_II(xlWB.ActiveSheet.Range("A1"))

xlWB.Close False
xlApp.Quit

End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 37048187
... run Demo_Excel_LastRealPopulatedColumn_II (below) in Word...
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37049388
Yes my code is designed to be passed a row or rows and will find the last populated column in that set of rows, rather than the whole worksheet - it could be much simpler if it just returned the last real populated column - just like @redmondb's code.  I have some other ways of doing this hidden away somewhere - I'll look out one of them and post if I can find it.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1800 total points
ID: 37051127
Found it - this uses Find to get the last column.  It is much faster than the other method in some cases because it does not have to loop through all the columns in the used range.  This can be very slow in the case of classic 'Excel boat' where perhaps Excel thinks the last used cell is in column XFD, when it's really in column H - you'd have to loop through 16,000+ columns to find the real one, whereas this one goes straight to it by using reverse find from the last cell by columns:
Public Function LastColByFind(ByRef rng As Excel.Range) As Long
'
    Dim rngLastCol As Excel.Range
    Dim rngLastRow As Excel.Range
    Dim wks As Excel.Worksheet
    
    Set wks = rng.Worksheet
    Set rngLastCol = wks.UsedRange
    
    wks.Cells.SpecialCells(xlCellTypeLastCell).Select
    
    Set rngLastCol = wks.Cells.Find(What:="*", _
        After:=wks.Cells(1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, MatchCase:=False, _
        SearchFormat:=False)
    
    If rngLastCol Is Nothing Then
        LastColByFind = 1
    Else
        LastColByFind = rngLastCol.Column
    End If
    

End Function

Open in new window

0
 

Author Comment

by:chtullu135
ID: 37051700
Thanks Andrew.  I'll give it a try on monday.
0
 

Author Closing Comment

by:chtullu135
ID: 37058557
I've gone with Andrews code.  But I wanted to show Brian my appreciation for his help.  In addition, Andrews use of the find what is similar to what I am using to find the row number - i.e.  I think this way there can be more consistent in the approach to calculating columns and row.
Dim r As Range
    Set r = s2.UsedRange
    Set r = r.EntireRow(r.Rows.Count)
    MsgBox r.Row
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37058585
chtullu135, thanks kindly. The main thing is whatever works for you!
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37059755
@chtullu135 - thanks for the points - on your last comment, just be careful using the UsedRange for doing this.  Although it's a quick way to to do it and will mostly be accurate, there are times when Excel gets the UsedRange wrong, and can make it much larger than the true used range.  The good thing about the code you originally posted and the other samples in this thread is that they get over this and look at where the last cell has a real value.  Things like cell formatting can cause the used range to spread enormously in cells that are really empty, causing the phenomenon sometimes called 'Excel bloat'.  To do rows you can use a very similar routine to the last one I posted, but using the SearchOrder xlByRows.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

850 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