12

I have 2 worksheets: Assets and Overview.

The functions are all put in a module.

Public Function GetLastNonEmptyCellOnWorkSheet(Ws As Worksheet, Optional sName As String = "A1") As Range
   Dim lLastRow        As Long
   Dim lLastCol        As Long
   Dim rngStartCell    As Range

   Set rngStartCell = Ws.Range(sName)
   lLastRow = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
           Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
           MatchCase:=False).Row

   lLastCol = Ws.Cells.Find(What:="*", After:=Ws.Range(rngStartCell), LookIn:=xlFormulas, _
           Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
           MatchCase:=False).Column

   Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))
End Function

From the worksheet Overview I call:

   Set RngAssets = GetLastNonEmptyCellOnWorkSheet(Worksheets("Assets"), "A1")

But I always get the error:

VBA: Getting run-time 1004: Method 'Range' of object '_Worksheet' failed

on the line:

 Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))

There is data on the worksheet Assets. The last used cell is W9 (lLastRow = 9 and lLastCol = 23).

Any idea why this is not working?

nealio82
  • 2,611
  • 1
  • 17
  • 19
user1856844
  • 151
  • 1
  • 1
  • 4

3 Answers3

25

Here is your problem statement:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol))

Evaluate the innermost parentheses:

ws.Cells(lLastRow, lLastCol)

This is a range, but a range's default property is its .Value. Unless there is a named range corresponding to this value, the error is expected.

Instead, try:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Range(Ws.Cells(lLastRow, lLastCol).Address)

Or you could simplify slightly:

Set GetLastNonEmptyCellOnWorkSheet = Ws.Cells(lLastRow, lLastCol)
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 1
    Is there any benefit in using `Range`? Would `Set GetLastNonEmptyCellOnWorkSheet = Ws.Cells(lLastRow, lLastCol)` work the same? – Sam Jan 06 '15 at 16:04
  • 1
    If by "work the same" you mean "get the same error", then yes it would work the same. See edited answer to omit the `ws.Range` method. – David Zemens Jan 06 '15 at 16:11
5

When I used blow code, the same error was occurred.

Dim x As Integer
Range(Cells(2, x + 13))

changed the code to blow, the error disappeared.

Dim x As Integer
Range(Cells(2, x + 13).Address)
bluetata
  • 577
  • 7
  • 12
-1

I found when the range I wanted to use not in the active sheet, then the error occurred. Active the sheet before you want to use range method in it, that fixed my issue.

Here is my code: I wanted to assign a range to an array.

keyDataSheet.Activate
LANGKeywords = keyDataSheet.Range(Cells(2, 1), Cells(LANGKWCount, 1))
COLKeywords = keyDataSheet.Range(Cells(3, 2), Cells(COLKWCount, 2))
currentWB.Activate
Julien Poulin
  • 12,737
  • 10
  • 51
  • 76
Ashton Fei
  • 114
  • 2
  • 7
  • It's generally frowned upon to `Activate` or `Select` objects in VBA, doing so can almost always be avoided and results in better, cleaner, more concise code that is less prone to this sort of error. See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – David Zemens May 25 '18 at 13:00