Calling Functions, Last Row, Last Column

[niki1210]

I am having a heck of a time finding the last columns and rows. I know I'm missing something simple and I've gotten this far with some help (Thanks KD!) but I still can't find the missing link. I see and understand all the articles, but I'm missing something obvious.

I have the following but it's giving me an error. Compile error, expected array" lstRow = lastRow(ws, "J")

This is in Module 4 I tried using a personal excel macro workbook but that isn't working either.

Option Explicit
Public Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function


Public Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
    With ws
        LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
    End With
End Function

This is in Module 3

Sub Module3()

Doing some other things and then 

Dim ws As Worksheet
Dim sortRng As Range, rngL As Range, rngJ As Range
Dim lstRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lstRow = lastRow(ws, "J")


With ws


    Set sortRng = .Range("B11", .Cells(lstRow, LastColumn(ws, 11)))
    Set rngJ = .Range("J11:J" & lstRow)
    Set rngL = .Range("L11:L" & lstRow)


    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=rngJ, SortOn:=xlSortOnValues, Order:=xlAscending, _
                CustomOrder:="AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
        .SortFields.Add Key:=rngL, SortOn:=xlSortOnValues, Order:=xlDescending, _
                DataOption:=xlSortNormal
        .SetRange sortRng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End With

How do I call functions? I'm sorry but I'm just not getting it. Thanks!!

 

[niki1210]

Anyone?

 

[MARK858]

You might need to look at the "Doing some other things and then" part as the below runs fine for me when in the same regular module or a different module in the same workbook....

Sub Module3()

'Doing some other things and then

Dim ws As Worksheet
Dim sortRng As Range, rngL As Range, rngJ As Range
Dim lstRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lstRow = lastRow(ws, "J")
Debug.Print lstRow

End Sub

Public Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function


Public Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
    With ws
        LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
    End With
End Function

 

[niki1210]

Thank you! I never thought to take that into consideration. It doesn't error out now. However, it's not sorting. I made sure the columns are correct in the worksheet. Any ideas? I even tried commenting out the number sort.

Dim ws As Worksheet
Dim sortRng As Range, rngL As Range, rngJ As Range
Dim lstRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lstRow = lastRow(ws, "J")




With ws




    Set sortRng = .Range("B11", .Cells(lstRow, LastColumn(ws, 11)))
    Set rngJ = .Range("J11:J" & lstRow)
    Set rngL = .Range("L11:L" & lstRow)




    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=rngL, SortOn:=xlSortOnValues, Order:=xlAscending, _
                CustomOrder:="AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
'        .SortFields.Add Key:=rngJ, SortOn:=xlSortOnValues, Order:=xlDescending, _
'                DataOption:=xlSortNormal
        .SetRange sortRng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




End With

 

[Rick Rothstein]

Option Explicit
Public Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function


Public Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
    With ws
        LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
    End With
End Function

You might find these two alternate functions to be useful as they will find the last row no matter what column it occurs in and the last column no matter what row it occurs in...

[table="width: 500"]
[tr]
	[td]Public Function LastRow(ws As Worksheet) As Long
  LastRow = ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
End Function

Public Function LastColumn(ws As Worksheet, Optional ReturnLetter As Boolean) As Variant
  LastColumn = ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column
  If ReturnLetter Then LastColumn = Split(Cells(1, LastColumn).Address, "$")(1)
End Function[/td]
[/tr]
[/table]

NOTE: I added an optional second argument to the LastColumn function, a Boolean, which if True will return the column letter designation and which if False or omitted will return the column number.

 

[niki1210]

Thank you! That will come in very handy. I would have had to hard code each spreadsheet. You saved me a ton of time. I appreciate it. It's working now. Thanks again!