Solved

Posted on 2011-10-28

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
```

15 Comments

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.

```
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
```

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

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
```

Regards,Brian.

```
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
```

```
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
```

Dim r As Range

Set r = s2.UsedRange

Set r = r.EntireRow(r.Rows.Count)

MsgBox r.Row

Title | # Comments | Views | Activity |
---|---|---|---|

excel forecast function | 1 | 28 | |

Sending email from Excel 2016 VBA and Outlook 2016 | 4 | 33 | |

MS Access Form Control Background Color Change Depending On How Long The String Text Length Is | 18 | 37 | |

Ensuring all processes are complete before continuing | 6 | 16 |

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

Connect with top rated Experts

**23** Experts available now in Live!