VBA Delete Row based on Cell value

[VBA learner ITG]

[FONT=&quot]Good evening peers,[/FONT]

[FONT=&quot]I am trying to figure out a logical way of deleting the highlighted rows in RED on the attached example workbook.[/FONT]

[FONT=&quot]the data is variable so it can change location and im trying to determine what logical approach i would take to delete the unncessary rows generated.[/FONT]

[FONT=&quot]I have tried with the below code and my logic is off and i could do putting this out there getting advice from my peers!

[/FONT]

[COLOR=#000000][FONT=Courier]Worksheet.Select "EHL"[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]myVal = Range("Z1").Value[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]LastRow = Cells(Rows.count, "K").End(xlUp).Row[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]Application.ScreenUpdating = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]Columns("Z").Insert[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]With Range("I6:I" & LastRow)[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .Formula = "=IF(I6=" & myVal & ",1,"""")"[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .Value = .Value[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    On Error Resume Next[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .SpecialCells(xlCellTypeBlanks).EntireRow.DELETE[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    Columns("Z:Z").Select[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    Selection.DELETE Shift:=xlToLeft[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    End With [/FONT][/COLOR]

https://zackcarter.wetransfer.com/d...17f44a8d9cf2acca95da81fd20171211183406/4ddd8c

 

[Rick Rothstein]

[FONT="]Good evening peers,[/FONT]

[FONT="]I am trying to figure out a logical way of deleting the highlighted rows in RED on the attached example workbook.[/FONT]

[FONT="]the data is variable so it can change location and im trying to determine what logical approach i would take to delete the unncessary rows generated.[/FONT]

[FONT="]I have tried with the below code and my logic is off and i could do putting this out there getting advice from my peers!

[/FONT]

[COLOR=#000000][FONT=Courier]Worksheet.Select "EHL"[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]myVal = Range("Z1").Value[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]LastRow = Cells(Rows.count, "K").End(xlUp).Row[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]Application.ScreenUpdating = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]Columns("Z").Insert[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]With Range("I6:I" & LastRow)[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .Formula = "=IF(I6=" & myVal & ",1,"""")"[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .Value = .Value[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    On Error Resume Next[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    .SpecialCells(xlCellTypeBlanks).EntireRow.DELETE[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    Columns("Z:Z").Select[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    Selection.DELETE Shift:=xlToLeft[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]    End With [/FONT][/COLOR]

https://zackcarter.wetransfer.com/d...17f44a8d9cf2acca95da81fd20171211183406/4ddd8c

That link takes us to a webpage that requires we click a button to agree to their Terms of Service and Cookies policy... most people are unwilling to click a link to an unknown webpage, but those who are will be leery of clicking any required buttons to proceed. Why don't you post your example worksheet to a known safe site like DropBox?

 

[VBA learner ITG]

Hi Rick,

thank you for your feedback, please see below

https://www.dropbox.com/s/0k82l9sm5lxzsfn/TEST DATA.xlsx?dl=0

 

[VBA learner ITG]

would the below logic work based on my issue:

In a row if columns "H" and "L" are blank and column "F" value is not repeated more than 2 times then delete the row and to begin after row 3.

Does anyone know of a forum that could help on this delete row logic based on text value not repeating more than 2 times?