VBA delete row based on cell value

Why do you want to delete those particular rows? In other words what's unique about them?

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.

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?

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

How do I tell if a row is an identifier?

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.

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.

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.

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.

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen in new window

You might also read my A Guide to Writing Understandable and Maintainable VBA Code article.

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.

Amazing solution delete row data based on column text value being duplicate

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