Code deletes entire Row, not only a part of it.

**Renta** · 09-04-2022, 09:21 AM

Hy!!

I have a macro that applies a filter for cells with date, selects the visible cells, and deletes them.
The issue I have is that it deletes the entire Row not only the ones between Q:W
Below the Rows I want to delete I have other data that I wish to also maintain.

This is the code I use:

Sub Clean_Breaks()
Range("Q14:W231").Select
Selection.AutoFilter

Range("Q17").Select
ActiveSheet.Range("$Q$14:$W$23").AutoFilter Field:=1, Criteria1:= _
">01/01/2022", Operator:=xlAnd
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireRow.Delete
ActiveSheet.ShowAllData
Selection.AutoFilter

Range("Q15").Select

End Sub

I attach a sample.

Thank you very much for your help.Forum_Breaks_Delete.xlsm

**TMS** · 09-04-2022, 09:47 AM

The middle word is the clue:

Please Login or Register  to view this content.

**Renta** · 09-05-2022, 04:49 AM

Thank you!

If I say just "Selection. Delete" it doesn?t work, because it selects as visual cells the entire row. If I include in the Macro "hide the columns A:P before selecting", it still selects them as visual data.

What would be the code for selecting the filtered data, (between Q and W, starting with Q17 till the last filtered data, and not affecting columns A:P, nor the rows below Q17:W... that are not filtered) and delete shift up?

I appreciate your help.

I don?t know much about VBA. I?m learning while doing and with YouTube videos, but I don?t find this part.

Thank you a lot.

Kind regards.

**TMS** · 09-05-2022, 05:19 AM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

**oeldere** · 09-05-2022, 05:32 AM

I am no VBA expert, but.

"Selection. Delete"

You used a space after the dot.

Remove the space and try again.

**Renta** · 09-05-2022, 05:49 AM

Hello, oeldere

In the code, it is written without space, and it doesn?t work because of the selection. When I ask it to select the visible data from the filter, it selects that part but includes the hole Row too. So, when I say (delete), it deletes the selected part.

My issue is selecting the adequate cells to be deleted.

I have tried with "Dim", but I?m doing some mistakes too.

Thank you a lot!

_____________________
My language is not English either. I hope I am explaining well.

**oeldere** · 09-05-2022, 06:02 AM

Then follow TMS advice "Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file."

**Renta** · 09-05-2022, 06:24 AM

In my first post, there is an attached file with my Excel. I include a picture of it in this poste too.

TMS kindly says that that's the clue, but I don?t have the solution to solve it. It should work with "Selection.Delete Shift:=xlUp", but, because it is applied to the wrong selection, it?s not working.

Thank?s a lot, again!!

Attachment 794860

**TMS** · 09-05-2022, 06:57 PM

Missed the attachment

Note: ' requires the "My Notes" cell to be NAMED as MyNotes
This is, initially, cell Q19. It will move as entries are added and is used to count the rows.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Renta** · 09-06-2022, 06:31 AM

It works!!! It works!!! It works!!!!!!!

Thanks a lot TMS for not quitting on me.

You made my day!!!

Thank you too oeldere for trying!!

I'm all a smile now.

Have a wonderful, wonderful day!!

**TMS** · 09-06-2022, 10:17 AM

You're welcome. Thanks for the rep.

Code deletes entire Row, not only a part of it.

LinkBack

Thread Tools

Rate This Thread

Display

Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Re: Code deletes entire Row, not only a part of it.

Thread Information

Users Browsing this Thread

Similar Threads

Issue - Single character deletes your entire post.

[SOLVED] Find and Replace value deletes entire cell contents

VBA that deletes entire rows if value is NOT found in part

[SOLVED] vba code that deletes entire row if there is a zero in that row of my highlighted area

[SOLVED] VBA that looks up a string (or part of) value and deletes the cell

Creating Excel Macro That Deletes Entire Row with Specific Text

Useful code that deletes entire rows based on formatting

Bookmarks

Bookmarks

Posting Permissions