asked on

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

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

Saqib Husain

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.

redmondb

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.

andrewssd3

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

Juan Velasquez

ASKER

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

redmondb

Thanks chtullu135. Andrew's code looks interesting.

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

SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

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.

redmondb

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

redmondb

... run Demo_Excel_LastRealPopulatedColumn_II (below) in Word...

andrewssd3

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.

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

Thanks Andrew. I'll give it a try on monday.

Juan Velasquez

ASKER

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

redmondb

chtullu135, thanks kindly. The main thing is whatever works for you!

andrewssd3

@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.