Find LastColumn: 2 methods tried, each gives different column as last

Question

This is my method #1. The correct result should yield Row 1 and Column 384. This method gives the correct result But it is highly inefficient with Activate/Select, etc.

Workbooks.Open Filename:=sDest, UpdateLinks:=0
Windows(sDestFile).Activate
Sheets(sDestTab).Select

'Find LastColumn
Dim colLast As Integer
With ActiveSheet
    colLast = Cells(rowTop, Columns.Count).End(xlToLeft).Column
End With

Then I created Method #2. This method is less of an eyesore, but it consistently gives me Row 1 and Column 386 as the answer (instead of Column 384). I cannot for the life of me figure out why a change in code would shift my LastColumn by 2.

Workbooks.Open Filename:=sDest, UpdateLinks:=0

'Find Last Column
Dim colLast As Integer
colLast = Workbooks(sDestFile).Worksheets(sDestTab).Cells(rowTop, Columns.Count).End(xlToLeft).Column

Excel reports the highest used cell in a column or row. It will (unfortunately) not reset even if you erase all traces of data in those cells. That is why it's difficult to find the active last cell used in columns and rows. I consider this behaviour a bug. — , Oct 01 '18 at 16:09
@peakpeak: that happens if you use `UsedRange`. I have already covered about it [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Siddharth Rout, Oct 01 '18 at 16:12
@Siddharth Rout: That looks promising. I have bookmarked that page for a later read. — , Oct 01 '18 at 16:15

score 1 · Answer 1 · answered Oct 01 '18 at 16:01

Use .Find. Try this

Dim lastcol As Long

With Workbooks(sDestFile).Worksheets(sDestTab)
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastcol = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Column
    Else
        lastcol = 1
    End If
End With

MsgBox lastcol

If you want to find the last column in Row 1 then try this

Dim wb As Workbook
Dim colLast As Long

Set wb = Workbooks.Open(Filename:=sDest, UpdateLinks:=0)

With wb.Sheets(sDestTab)
    colLast = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

score 0 · Answer 2 · answered Oct 01 '18 at 20:15

when I want to find the last row or column containing the value:

Option Explicit

Public Function Row_Last(ws As Worksheet) As Long
    On Error Resume Next
    Row_Last = _
    ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Row_Last = 0 Then Row_Last = 1
End Function

Public Function Col_Last(ws As Worksheet) As Long
    On Error Resume Next
    Col_Last = _
    ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    If Col_Last = 0 Then Col_Last = 1
End Function

Find LastColumn: 2 methods tried, each gives different column as last

2 Answers2