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

[Renta]

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]

The middle word is the clue:

 Selection.EntireRow.Delete

[Renta]

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]

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

[oeldere]

I am no VBA expert, but.


"Selection. Delete"

You used a space after the dot.

Remove the space and try again.

[Renta]

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]

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]

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]

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.

Sub BREAK_START()
'
' BREAK_START Macro
'
' Works OK

    Range("Q15").Value = Date
    Range("R15").Value = "My name"
    Range("S15").Value = "XY"
    Range("T15").Value = Format(Now, "hh:mm")
    Range("W15").Value = "Break"
    Range("V15").Formula = "=IF((IF(+(RC[-1]-RC[-2])=0,""time.."",RC[-1]-RC[-2]))<0,""St>En!"",IF(+(RC[-1]-RC[-2])=0,""St=En!"",RC[-1]-RC[-2]))"
    
    Range("U15").Select
    
End Sub
Sub WC_START()
'
' BREAK_START Macro
'
' Works OK

    Range("Q15").Value = Date
    Range("R15").Value = "My name"
    Range("S15").Value = "XY"
    Range("T15").Value = Format(Now, "hh:mm")
    Range("W15").Value = "WC"
    Range("V15").Formula = "=IF((IF(+(RC[-1]-RC[-2])=0,""time.."",RC[-1]-RC[-2]))<0,""St>En!"",IF(+(RC[-1]-RC[-2])=0,""St=En!"",RC[-1]-RC[-2]))"
    
    Range("U15").Select
    
End Sub

Sub BREAK_END()
'
' BREAK_END Macro
'
' Works OK

If Range("T15") = "" Then
    MsgBox "Write the START hour first, please."
Else
    Range("U15").Value = Format(Now, "hh:mm")
    Range("Q15:W15").Copy Range("Q16")
    Range("Q16:W16").Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("V16:V17").Locked = False
    Range("V16:V17").FormulaHidden = True
    Range("Q15:U15").ClearContents
    Range("W15").ClearContents
    
    Range("U17").Select
End If

End Sub

Sub Clean_Breaks()
'
' Clean_Breaks Macro
'

' requires the "My Notes" cell to be NAMED as MyNotes
Dim lLR As Long
Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
    
lLR = Range("MyNotes").Row - 1
lrows = wsf.Count(Range(Cells(17, "Q"), Cells(lLR, "Q")))
If lrows >= 1 Then
    Range("Q17").Resize(lrows, 7).Delete shift:=xlUp
End If
    
    Range("Q15").Select
End Sub

Sub BREAKE_COPY()
'
' BREAKE_COPY Macro
'

' requires the "My Notes" cell to be NAMED as MyNotes
Dim lLR As Long
Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
    
lLR = Range("MyNotes").Row - 1
lrows = wsf.Count(Range(Cells(17, "Q"), Cells(lLR, "Q")))
If lrows >= 1 Then
    Range("Q17").Resize(lrows, 7).Copy
End If

End Sub

[Renta]

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]

You're welcome. Thanks for the rep.