Ask a new question

VBA - Selection.EntireRow.Delete doesn't work

Hi together,
I want to select and delete rows based on their value.
I tested the code in a new sheet and it works fine. But it doesn't work in my actual sheet where I want to use it.
The code stop at Selection.EntireRow.Delete in yellow

How I can trace or figure out which condition exactly cause this in my sheet?
There are no merged cells in the applicable range.

My Code:

Range("A69:A3000").Select
    For Each Cell In Selection
        If Cell.Value = "YES" Then
            Rows(Cell.Row).ClearContents
   End If
   Next

    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete

I apreciate any hint

Andi

 

Hi Maverick 

I answering from my phone so I haven't tested it.

Selecting cells prompt to errors 

No point to clear contents of cells that has "YES",  to then delete the entire row.

Go straight to delete the row.

So Please, try 

Sub DeleteYesRows()

Dim Rng, Cl As Range 

Set Rng=ActiveSheet.Range("A69:A3000")

For Each Cl in Rng

If Cl.Value = "YES" Then

Cl.EntireRow.Delete

End If

Next Cl

End Sub

Hope this helps you 

Do let me know if you need more help 

Please,
Consider marking this reply as the answer to your question if it does so.
It will help others in the community with similar problems or questions.
Thank you in advance


Regards
Jeovany CV

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thank you for your replies. Obviously there are many ways to proceed. For me worked this then:

Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

    If Cells(i, 1).Value = "YES" Then
        Rows(i).Delete
    End If
Next

But I still don't get why its not working with the .selection. In a virgin sheet it works, but not in my actual sheet. I still want to know why.

So, how I'm able to trace or find the root cause if I become such an error? Is there any function to show me which term isn't fulfilled?

Andi

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi There ...

Can you add up a screenshot of exact error message.

If you feel my reply has helped you then as a token of appreciation please do vote and mark as answer as it would help other community members.


-- VS_Excel

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 22, 2025 Views 4,592 Applies to: