I don't understand this error message!

[TheWennerWoman]

Hello,

I have the following code

Dim lrow, low1, lrow2 As Long

lrow = Sheet6.Cells(Sheet6.Rows.Count, "B").End(xlUp).Row
lrow1 = Sheet8.Cells(Sheet8.Rows.Count, "B").End(xlUp).Row
lrow2 = Sheet13.Cells(Sheet13.Rows.Count, "A").End(xlUp).Row

If lrow2 > 1 Then
Sheet13.Range("A2:A" & lrow2).EntireRow.ClearContents  ' this line is erroring
End If

lrow2 = 571

I am getting Run Time Error 1004: method 'range' of object '_Worksheet' failed?

I'm sure this macro gets run every month without errors. Any thoughts?

Thank you in advance.

 

[Jimmypop]

Hi there

I tested very quickly on my side and it is working. Could be that you do not have a Sheet named Sheet13? Guessing you are working with the codenames of the sheet?

I did pick up from your code above that you need to change: Missing an r at lrow1...

Dim lrow, low1, lrow2 As Long

to

Dim lrow, lrow1, lrow2 As Long

 

[DanteAmor]

I don't see what could be the error. I already tried it and it works correctly.

But, I would like to recommend the following:
1. Use the Option Explicit statement at the beginning of all your code, to check the declaration of all your variables. If you had it, it would be marking an error in the variable lrow1 , since it is not declared, because in your declaration you have: low1

2. You must set the type to each variable, like this:
Dim lrow As Long, lrow1 As Long, lrow2 As Long
Otherwise, the first 2: lrow and lrow1 will be of type variant.

For example:

Option Explicit

Sub test()
  Dim lrow As Long, lrow1 As Long, lrow2 As Long
  
  lrow = Sheet6.Cells(Sheet6.Rows.Count, "B").End(xlUp).Row
  lrow1 = Sheet8.Cells(Sheet8.Rows.Count, "B").End(xlUp).Row
  lrow2 = Sheet13.Cells(Sheet13.Rows.Count, "A").End(xlUp).Row ' this line is erroring
  
  If lrow2 > 1 Then
    Sheet13.Range("A2:A" & lrow2).EntireRow.ClearContents
  End If
End Sub

You could upload a copy of your file to a free site such www.dropbox.com or google drive to review it.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[TheWennerWoman]

I don't see what could be the error. I already tried it and it works correctly.

But, I would like to recommend the following:
1. Use the Option Explicit statement at the beginning of all your code, to check the declaration of all your variables. If you had it, it would be marking an error in the variable lrow1 , since it is not declared, because in your declaration you have: low1

2. You must set the type to each variable, like this:
Dim lrow As Long, lrow1 As Long, lrow2 As Long
Otherwise, the first 2: lrow and lrow1 will be of type variant.

For example:

Option Explicit

Sub test()
  Dim lrow As Long, lrow1 As Long, lrow2 As Long
 
  lrow = Sheet6.Cells(Sheet6.Rows.Count, "B").End(xlUp).Row
  lrow1 = Sheet8.Cells(Sheet8.Rows.Count, "B").End(xlUp).Row
  lrow2 = Sheet13.Cells(Sheet13.Rows.Count, "A").End(xlUp).Row ' this line is erroring
 
  If lrow2 > 1 Then
    Sheet13.Range("A2:A" & lrow2).EntireRow.ClearContents
  End If
End Sub

You could upload a copy of your file to a free site such www.dropbox.com or google drive to review it.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Thanks all, there is a Sheet13 so what I did was prefixed the above with

Sheet13.Select

and it worked fine. Bizarre.