VBA delete row based on cell value

Good evening peers,

I am trying to figure out a logical way of deleting the highlighted rows in RED on the attached example workbook.

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.

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!

Worksheet.Select "EHL"

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

Open in new window


Any advice would be appreciated.
TEST-DATA.xlsx
zack carterProject AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Why do you want to delete those particular rows? In other words what's unique about them?
0
zack carterProject AnalystAuthor Commented:
The rows  highlighted are not required because they dont generate any barcodes with prices.

Once the rows in Red are deleted you can see that it forms a logical format which is needed by our external agency to print barcode tickets.

The problem at the moment is the rows in Red is generated with no associated barcodes and ive tried various row delete codes and also filter method and i cant crack this when the data is variable and can be random in terms at the red downs are not set at its current location as it cold be in the middle of the data or at the end or at different sequence in the data set.

Any advice is appreciated.
0
Martin LissOlder than dirtCommented:
Are you saying that if columns "H" and "L" are blank that the row should be deleted? If so should row 12 also be deleted?
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

zack carterProject AnalystAuthor Commented:
Row 12 and 13 are required because they are the identifer that there is barcodes for that Dept/Category.

the format thats required should be like the attached.
TEST-DATA---good-example.xlsx
0
Martin LissOlder than dirtCommented:
How do I tell if a row is an identifier?
0
zack carterProject AnalystAuthor Commented:
It would be from column F "Category" as row 12 and 13 are the identifer to our printer that there is barcodes created for that category.

As from the Test Data - good example there are "Clip Strips" barcodes generated for that category.
0
Martin LissOlder than dirtCommented:
Sorry but I still don't understand and in order for me to help I need you to define (using cell values) when a row should be deleted. In other words something like this .

In a row if columns "H" and "L" are blank and column "F" is <specify some condition(s)> then delete the row.

I'm going out for a couple of hours and I'll be back if someone else doesn't resolve this for you first.
0
zack carterProject AnalystAuthor Commented:
Would a delete row if value in colum  F doesnt appear more than 2 times and to begin after row 3 ?

row 1 header row - is needed
row 2 & row 3 is store header which is needed.
0
zack carterProject AnalystAuthor Commented:
In a row if columns "H" and "L" are blank and column "F" text value is  not repeated more than 2 times in column F  then delete the row and to begin after row 3.
0
zack carterProject AnalystAuthor Commented:
would something like the below function work:

If Application.WorksheetFunction.CountIf(Columns(6), Range("A" & x).Text ,< 3 Then
    Range("A" & x).EntireRow.Delete
End If

Open in new window

0
Martin LissOlder than dirtCommented:
Try this.
Sub RenoveUnNeeded()
Dim lngLastRow As Long
Dim lngRow As Long

Application.ScreenUpdating = False

lngLastRow = Range("A1048576").End(xlUp).Row
With Sheets("EHL")
    lngLastRow = .Range("A1048576").End(xlUp).Row
    For lngRow = lngLastRow To 4 Step -1
        If Application.WorksheetFunction.CountIf(.Range("F:F"), .Cells(lngRow, "F")) < 3 Then
            If .Cells(lngRow, "H") = Empty And .Cells(lngRow, "L") = Empty Then
                .Cells(lngRow, "A").EntireRow.Delete
            End If
        End If
    Next
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zack carterProject AnalystAuthor Commented:
Hi Martin,

Thank you, your vba script iworked.

I was trying with the below and  failed miserably.

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

lr = Range("A" & Rows.count).End(xlUp).Row 'make sure you use a column with key data to find last row

lc = Range("A3").End(xlToRight).Column + 1 'find last col based on title headers

X = "=AND(ISBLANK(H4),ISBLANK(L4),COUNTIF($F$4:$F$" & lr & ",F4)<2)" 'formula to determine deletion criteria.

Cells(4, lc) = X

On Error Resume Next

Cells(4, lc).AutoFill Destination:=Range(Cells(4, lc), Cells(lr, lc))

On Error GoTo 0

Range(Cells(3, lc), Cells(lr, lc)).Copy

Range(Cells(3, lc), Cells(lr, lc)).PasteSpecial xlPasteValues

Range(Cells(3, 1), Cells(3, lc)).AutoFilter field:=13, Criteria1:="TRUE"

Range("A4:A" & Rows.count).EntireRow.DELETE

Cells(1, 13).EntireColumn.DELETE

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

Open in new window

0
Martin LissOlder than dirtCommented:
Please don't forget to close this question but (and I hope you don't mind) here are a few comments about you code.

1) Always place OPTION EXPLICIT at the top of Module1 or any other code, sheet, class or userform module.
2) Explicitly define all your variables
3) Always refer to the sheet where the data is. For example don't do lr = Range("A" & Rows.count).End(xlUp).Row because VBA might run against the wrong sheet. Instead do something like the following
With Thisworkbook.Sheets("EHL")
    lr = .Range("A" & Rows.count).End(xlUp).Row ' Note the dot after the equal sign
End with

Open in new window

or

Dim ws As Worksheet
Set ws = Thisworkbook.Sheets("ESL")
With ws
    lr = ws.Range("A" & Rows.count).End(xlUp).Row
End With

Open in new window


You might also read my A Guide to Writing Understandable and Maintainable VBA Code article.
1
zack carterProject AnalystAuthor Commented:
Hi Martin,

Without speaking to a master like you I wouldn't have resolved this and would still be looking at the basic fundamental approach to my issue.

Thank you for your help and feedback.
0
zack carterProject AnalystAuthor Commented:
Amazing solution delete row data based on column text value being duplicate
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.