Run-Time Error 1004:

[Oberon70]

Hi,

I am a bit confused to why this error is occurring as it was working a few minutes ago.

Sub EditTempTble()

Dim LastCell As String
Dim LastRow As Double
Dim iCntr As Long

LastCell = wsTmpData.Range(FindLast(3)).Address
LastRow = Range(LastCell).Row

For iCntr = LastRow To 1 Step -1
    If Trim(Cells(iCntr, 1)) = "" Then
        Rows(iCntr).Delete
    End If
Next

End Sub

The error is happening on the below line:

Rows(iCntr).Delete

The last row is correct:

Please let me know if you need the rest of the code? to assist? but the other subs in this module are running like I wanted.

 

[6StringJazzer]

Is the sheet protected? That will cause a 1004 error. I see nothing else in the code that would cause an error.

By the way, this is not causing an error but you should declare LastRow as Long, not Double.

 

[Fluff]

What is the actual error message? 1004 can be a lot of things.

 

[Oberon70]

Sorry, the full error message is:

And the issue is the code didn't switch to the tempData sheet I copied the table too from Data that is protected.

 

[Oberon70]

I have added wsTmpData.activate to the code and that has solved the issue. I am not sure if that is the best way, but it is working. I didn't think about the protected pages thank you.

 

[Fluff]

Rather than activating the sheet you can use

Sub EditTempTble()

Dim LastCell As String
Dim LastRow As Double
Dim iCntr As Long
With wsTmpData
   LastCell = .Range(FindLast(3)).Address
   LastRow = .Range(LastCell).Row
   
   For iCntr = LastRow To 1 Step -1
       If Trim(.Cells(iCntr, 1)) = "" Then
           .Rows(iCntr).Delete
       End If
   Next
End With
End Sub

 

[Oberon70]

thank you

 

[Fluff]

You're welcome.

 

[6StringJazzer]

Rather than activating the sheet you can use

Sub EditTempTble()

Dim LastCell As String
Dim LastRow As Double
Dim iCntr As Long
With wsTmpData
   LastCell = .Range(FindLast(3)).Address
   LastRow = .Range(LastCell).Row
 
   For iCntr = LastRow To 1 Step -1
       If Trim(.Cells(iCntr, 1)) = "" Then
           .Rows(iCntr).Delete
       End If
   Next
End With
End Sub

This still won't work because the custom function FindLast will operate on ActiveSheet unless an optional argument is provided giving the sheet name. This is discussed in another thread where the code for FindLast was posted.