Getting Error 438 when Running macro from Sht1 to delete row on Sht2

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Everyone,

Getting a runtime 438 when running this.

I believe everything is defined...

How would you write this properly if the macro is ran from Sheet "SignoffChecks"???

Thanks for any help

VBA Code:
Sub SignOffDeleteRow()

Dim r As Long
Dim LastRow As Long
LastRow = Worksheets("Sign_Off_Selection").Cells(Rows.Count, "A").End(xlUp).Row
For r = Worksheets("Sign_Off_Selection").LastRow To 1 Step -1
If Worksheets("Sign_Off_Selection").Cells(r, 8) = Worksheets("SignoffChecks").Range("H11") Then
Rows(r).Delete
End If
Next r

End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Try this change
from
LastRow = Worksheets("Sign_Off_Selection").Cells(Rows.Count, "A").End(xlUp).Row
to
LastRow = Worksheets("Sign_Off_Selection").Cells(Rows.Count,1).End(xlUp).Row
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
This line
VBA Code:
For r = Worksheets("Sign_Off_Selection").LastRow To 1 Step -1
should just be
VBA Code:
For r = LastRow To 1 Step -1

There is nothing wrong with
VBA Code:
Cells(Rows.Count, "A")
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hey Dermot,

Thanks for your help, but I'm still getting the same error highlighting
VBA Code:
For r = Worksheets("Sign_Off_Selection").LastRow To 1 Step -1
 

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

did you see the other reply to your question?
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hey Mark858,
Is that your area code? I lived in San Diego too once upon a time.

I changed that line, and don't get an error, but nothing happens.
Is there a way to define the sheet Last Row is on?
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Yes, thanks Dermont. I saw it after I posted my reply to you.
Still having an issue though
 

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Yes, thanks Dermont. I saw it after I posted my reply to you.
Still having an issue though
It's hard to say without seeing all of it, but could it be this?

Worksheets("Sign_Off_Selection").Rows(r).Delete
instead of
Rows(r).Delete
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Basically I have a range on sheet Sign_Off_Selection from A:H (Rows are ever expanding) with column H being =row()-1

Userform is on sheet SignoffChecks. A macro first runs to determine the row that needs deleting, it temporally pastes that row number to Worksheets("SignoffChecks").Range("H11")
then the macro in question takes over.

This would all need to be done from the form on SignoffChecks
 

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
That did it!
Thanks Dermot!!

Also thanks to Mark858, I changed the line you suggested.

Heck yeah! Way to end the night
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top