VBA Macro run from another sheet

[clohuff]

I have a macro that allows me to delete certain rows based off a specific text value from a specific sheet only, this seems to only work as long as I am active on the sheet named Apple however I want to be able to run a macro enabled button while I am on a different sheet and I keep receiving an error.

For reference the sheet named where I need the rows to be deleted from is named Apple and its deleting rows if column A contains the verbiage blue

My code:

Sub DeleteRow

lastrow= ThisWorkbook.Worksheets("Apple").Cells(Row.Count,1).End(xlUp).Row

For i=lastrow To 2 Step by -1
If ThisWorkbook.Worksheets("Apple").Cells(i,1).Value="Blue" Then

Rows(i).Delete

End If

ThisWorkbook.Worksheets("Apple").Cells (1,1).Select

Next

End Sub

Any suggestions would help thank you!

 

[Micron]

Put the code in a general module and use full sheet reference. You could use ActiveSheet instead, but if you do that and code runs it could do bad things to the sheet that happens to be active but isn't the right sheet. You can simply use the button click event, in which case you don't need a procedure named Sub DeleteRow.
Please use code tags ("VBA" button on posting toolbar) to maintain code indentation and readability.

 

[clohuff]

Put the code in a general module and use full sheet reference. You could use ActiveSheet instead, but if you do that and code runs it could do bad things to the sheet that happens to be active but isn't the right sheet. You can simply use the button click event, in which case you don't need a procedure named Sub DeleteRow.
Please use code tags ("VBA" button on posting toolbar) to maintain code indentation and readability.

What is a general module or what would be the full sheet reference?

Sub DeleteRow

lastrow= ThisWorkbook.Worksheets("Apple").Cells(Row.Count,1).End(xlUp).Row

For i=lastrow To 2 Step by -1
If ThisWorkbook.Worksheets("Apple").Cells(i,1).Value="Blue" Then

Rows(i).Delete

End If

ThisWorkbook.Worksheets("Apple").Cells (1,1).Select

Next

End Sub

 

[Micron]

You can do research to save time, yes?

I missed that you're already using explicit sheet reference.
ThisWorkbook.Worksheets("Apple")

Sorry I missed that. Sometimes my eyes just don't see things with unformatted code.

 

[clohuff]

You can do research to save time, yes?

View attachment 90066

I missed that you're already using explicit sheet reference.
ThisWorkbook.Worksheets("Apple")

Sorry I missed that. Sometimes my eyes just don't see things with unformatted code.

so to confirm your recommendation was to use full sheet reference, which is what my code already entails, so any other suggestions to how I can run my code while being on another sheet? You mentioned active sheet was probably not the best option.

 

[Micron]

so any other suggestions to how I can run my code while being on another sheet?

Yes.

Put the code in a general module

aka "standard" module. Code in a standard module can be called from anywhere - sheet, userform, other code contained in sheet or userform. The code in your standard module might look like

Sub DeleteRow

lastrow= ThisWorkbook.Worksheets("Apple").Cells(Row.Count,1).End(xlUp).Row
For i=lastrow To 2 Step by -1
   If ThisWorkbook.Worksheets("Apple").Cells(i,1).Value="Blue" Then Rows(i).Delete
' I don't see the point in this next line, especially in a loop where nothing happens to/with the selection
   ThisWorkbook.Worksheets("Apple").Cells (1,1).Select
Next

End Sub

If you put a button on the sheet I think I'd just put the button click code in the sheet module (which is probably where you've got it now). It might look like
Sub NameThatIgaveToButton_Click()
DeleteRow
End Sub
I'm assuming that no matter what sheet you're on, you want the code to affect only the sheet named "Apple"

For reference the sheet named where I need the rows to be deleted from is named Apple

If you want the same code to work on any sheet you're on, that is a different matter. In that case your button click code would have to be in every sheet module and it would have to pass the name of the sheet to the standard module code, which then would need to be modified. Your other option in that case would be to put button click code on every sheet that runs something like I showed but you'd need to provide the correct sheet name for every procedure. So 10 sheets, 10 copies of the code (one on each sheet) with the correct sheet name in it. A lot of guessing and writing for different scenarios here, which is a bit tedious for me.

I see you used the code tags (thanks) but how I've indented it is how it should be, especially in long procedures. Indentation defines groups/relationships in blocks of code and makes it so much easier to decipher.

 

[clohuff]

Yes.

aka "standard" module. Code in a standard module can be called from anywhere - sheet, userform, other code contained in sheet or userform. The code in your standard module might look like

Sub DeleteRow

lastrow= ThisWorkbook.Worksheets("Apple").Cells(Row.Count,1).End(xlUp).Row
For i=lastrow To 2 Step by -1
   If ThisWorkbook.Worksheets("Apple").Cells(i,1).Value="Blue" Then Rows(i).Delete
' I don't see the point in this next line, especially in a loop where nothing happens to/with the selection
   ThisWorkbook.Worksheets("Apple").Cells (1,1).Select
Next

End Sub

If you put a button on the sheet I think I'd just put the button click code in the sheet module (which is probably where you've got it now). It might look like
Sub NameThatIgaveToButton_Click()
DeleteRow
End Sub
I'm assuming that no matter what sheet you're on, you want the code to affect only the sheet named "Apple"

If you want the same code to work on any sheet you're on, that is a different matter. In that case your button click code would have to be in every sheet module and it would have to pass the name of the sheet to the standard module code, which then would need to be modified. Your other option in that case would be to put button click code on every sheet that runs something like I showed but you'd need to provide the correct sheet name for every procedure. So 10 sheets, 10 copies of the code (one on each sheet) with the correct sheet name in it. A lot of guessing and writing for different scenarios here, which is a bit tedious for me.

I see you used the code tags (thanks) but how I've indented it is how it should be, especially in long procedures. Indentation defines groups/relationships in blocks of code and makes it so much easier to decipher.

Thank you for your time.