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?
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.