How to get the lastcolumn with data using VBA

Question

I cannot get my code to properly identify the last column with data. The Last Column should be $Q$9 but when running the function I get $M$9 as my Last Column. What am I doing wrong?

Code

Function Q3calls()

Set wb = ActiveWorkbook
Set ws = wb.Sheets("clientmenu")

lastrow = ws.Range("M" & ws.Rows.Count).End(xlUp).Row
lastcol = ws.Cells(8, Columns.Count).End(xlToLeft).Column

With ws
    Set rng = .Range(.Cells(8, 13), .Cells(lastrow, lastcol))
End With

Q3calls = Application.WorksheetFunction.CountIfs(rng, ">=" & wb.Sheets("sheet1").Range("A67"), rng, "<=" & wb.Sheets("Sheet1").Range("B67")) 'q3

Exit Function

End Function

Screen Shot of Range

Possible duplicate of [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Tom, Aug 02 '19 at 10:39
Your code is looking at `Row` 8 and you're expecting the result of `Row` 9 — Tom, Aug 02 '19 at 10:40
@Ron Rosenfeld the last row always changes, it is not always 9 — PP8, Aug 02 '19 at 17:12

Tom · Answer 1 · 2019-08-02T11:13:28.923

0

Replace

lastrow = ws.Range("M" & ws.Rows.Count).End(xlUp).Row
lastcol = ws.Cells(9, Columns.Count).End(xlToLeft).Column

With

With ws
    lastrow = .Cells(.Rows.Count, "M").End(xlUp).Row
    lastcol = .Cells(9, .Columns.Count).End(xlToLeft).Column
End With

I'd also recommend defining your variables which you don't seem to have done in your function

Function Q3calls()
   Dim wb as Workbook
   Dim LastRow as Long, LastCol as Long
   Dim rng as Range

   set wb = ActiveWorkbook

    With wb.Sheets("clientmenu")
        LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        LastCol = .Cells(9, .Columns.Count).End(xlToLeft).Column

        Set rng = .Range(.Cells(8, 13), .Cells(LastRow, LastCol))
    End With

    With wb.Sheets("Sheet1")
        Q3calls = Application.WorksheetFunction.CountIfs(rng, ">=" & .Range("A67"), rng, "<=" & .Range("B67")) 'q3
    End With

End Function

edited Aug 02 '19 at 11:13

answered Aug 02 '19 at 10:41

Tom

9,725
3
31
48

Tom you are not qualifying the sheet on `Columns.Count` so if `ws` is not the active sheet this will fail. – Damian Aug 02 '19 at 11:06
@Damian - copy paste jobby. Have updated with fully qualified. – Tom Aug 02 '19 at 11:09
@tom I would like my range to start on `$M$8`. Your code is giving my values starting from `$A$1`. The last row is always changing as I add more customers to the list. Am I missing something? – PP8 Aug 02 '19 at 17:07

score 0 · Answer 2 · answered Aug 02 '19 at 10:54

You can use this Function also:

Function LastCol(Sh As Worksheet)
    On Error Resume Next
    LastCol = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Then LastColumn would be

lastcol = LastCol(wb.Sheets("clientmenu"))

How to get the lastcolumn with data using VBA

2 Answers2