Called Sub only works when stepping through Code

Anfinsen

Board Regular
Joined
Apr 11, 2023
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi All, first time poster here, I hope i get this right. Thanks in advance for any help! Running Office 365 desktop apps.

I am calling the sub below, as part of a loop, and it only performs the delete rows part when I step through it using F8. When i run the entire code where it calls this, it seemingly ignores this step. I am thinking it has something to do with the way this is written.
I only want to retain records that are a value of 5 and under, in column I. Below is the code in question.

Private Sub DeleteRowsOver5()

With Sheets("Worksheet")

Sheets("Worksheet").Range("i2").Value = 1
Sheets("Worksheet").Range("i3").Value = "=I2+1"
Sheets("Worksheet").Range("I3:I" & Cells(Rows.Count, 8).End(xlUp).Row).FillDown

Dim r As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "I") > 5 Then
Rows(r).Delete

End If

Next r
End With

End Sub
 

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
Your macro works fine here. Check spellings and sheet references in your macro.
 
Upvote 0
Hi Logit,
Thanks for checking this out for me. Whether I call this code, or emebed it as a step in the loop, it still ignores this step when running, only works when I manually step through the code.
 
Upvote 0
What is the name of the module that module that you have placed this code in?
 
Upvote 0
That is not the question I asked.
I did not ask what Procedure it is called in, I asked what Module your code is placed in.
See: What is a VBA Module? - Automate Excel

What Module did you place each of these procedures in (both "DeleteRowsOver5" and "MoveLines")?
 
Upvote 1
That is not the question I asked.
I did not ask what Procedure it is called in, I asked what Module your code is placed in.
See: What is a VBA Module? - Automate Excel

What Module did you place each of these procedures in (both "DeleteRowsOver5" and "MoveLines")?
I believe the answer to your question would be "General"?
I had this as a seperate module, outside of the worksheets. Once I moved it into a worksheet, it worked fine.
My apologies for not framing my response correctly. I didn't understand your question. Your question did however lead me in the right direction, so I thank you kindly for that Sir.
Have a great day, and thanks again for taking the time to answer my post.
 
Upvote 0
You are welcome.

Note that it should work fine, assuming that you are referencing the sheet along with your range values. You had not done that in this section:
VBA Code:
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For r = lastrow To 1 Step -1
    If Cells(r, "I") > 5 Then
        Rows(r).Delete
    End If
If you leave out the "Sheet" reference, before the range, it defaults to whatever the active sheet is at the time.

Note that you have this block of code within your "With" block, i.e.
VBA Code:
With Sheets("Worksheet")
    ...
End With

If you wanted that part of the code to pertain to the same "Worksheet", you just need to put a period in front of your range references, and it will "inherit" the specified sheet from your With statement, i.e.
VBA Code:
lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row
For r = lastrow To 1 Step -1
    If .Cells(r, "I") > 5 Then
        .Rows(r).Delete
    End If
 
Upvote 1
Solution

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top