vba script to find cells with specified criteria and delete the rows

[dagnnorb]

I have no VB background and have been working on a template spreadsheet for 2 weeks. I have stumbled through a lot of the VB scripting i needed so far by finding samples online that I could figure out how to modify to meet my needs. BUT....

I have been banging my head for almost 2 days trying to write a script to remove rows of data based on 2 criteria. The data is in multiple columns ( I am making a template excel file that I will use to create files for different product, the column count varies from 34 columns up to 298). Additional data is added on a daily basis as additional rows.

I have a named data range created on my spreadsheet called "dataonly" that includes column headers and the data using an initial starting cell (Q10) that never changes and using offset to dynamically define the bottom right most data cell used.

Other variables on my spreadsheet that could be useful:

cell H2 is the total number of columns in the "dataonly" named range
Cell H5 is the total number of rows in the "dataonly" named range

The data I need to search , find and delete the row in the "dataonly" range is:

1. the word "Not"
2. any value over 250

I tried using using Autofilter and looping it through the columns as follows:

Sub testing()
Dim pincount As Integer
Dim counter As Integer
Dim rows As Integer


pincount = Range("H2").Value
rows = Range("H5").Value + 1
counter = 0


With ActiveSheet
.AutoFilterMode = False
Do While counter < pincount
With Range(Cells(10, 17 + counter), Cells(10 + rows, 17 + counter))
.AutoFilter counter + 1, "not"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delet
End With
counter = counter + 1
Loop
.AutoFilterMode = False
End With
End Sub

But it only finds the cells found in the first column and removes the row. It goes through the loop and looks at all other columns, but it does not find the cells with the criteria I am looking for and deleting the rows.


I also started looking at Advanced filter, but I am not getting far on it.

Sub testing2()


With Range("dataonly")
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("GE1"), Unique:=True
On Error Resume Next
.Offset(1).Resize(.rows.Count - 1).EntireRow.Delete
End With
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub

This deletes all the data rows.

I am about to bang my head against a wall. I know I am sort of close on both techniques, but I can not see what my problem is and after staring at it for 2 days trying anything I can think of...

Thank you for your help and suggestions.

Steve

P.S. please remember that I have no VB background, so please bare with me with any stupid questions I may have, and the more detailed the explanation of the script would be so useful for me.

 

[dagnnorb]

Well no one had any ideas, but I figured out how to do it. It may not be the cleanest or fastest but it works...


Sub testing()
Dim pincount As Integer
Dim counter As Integer
Dim rows As Integer


pincount = Range("H2").Value
rows = Range("H5").Value + 1
counter = 0


With ActiveSheet
.AutoFilterMode = False
Do While counter < pincount
With Range("dataonly") '(Cells(10, 17 + counter), Cells(10 + rows, 17 + counter))
.AutoFilter counter + 1, "Not", xlOr, ">250"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
counter = counter + 1
.AutoFilterMode = False
Loop


End With
End Sub

 

[dagnnorb]

same script as before but with annotations to help others to figure out how this may work.


Sub data_scrub()


Dim pincount As Integer 'number of contacts in connector also defines number of columns of data
Dim counter As Integer 'counter to keep track of when to get out of do while loop
Dim rows As Integer 'number of rows of data


pincount = Range("H2").Value 'read in pincount value
rows = Range("H5").Value + 1 'read in rows value
counter = 0 'set counter value to 0


With ActiveSheet
.AutoFilterMode = False 'turns off visibility autofilter arrows in cells
Do While counter < pincount 'start of do loop to step through each column of data to look for criteria to delete rows
With Range("dataonly") 'defines range of what data to look at "dataonly" is named range defined in spreadsheet
.AutoFilter counter + 1, "Not", xlOr, ">250" 'finds cells with EITHER word "not" in cell or cell with value greater than 250 (counter+1 defines column being looked in)
On Error Resume Next 'in case there are no cells found with either criteria, it continues instead of causing issues
.Offset(1).SpecialCells(12).EntireRow.Delete 'selects rows and deletes them, offset(1) is so header is not deleted
End With
counter = counter + 1 'bump counter by one
.AutoFilterMode = False 'turns off visibility autofilter arrows in cells again, without, it will only look at first column for data and find it
Loop


End With
End Sub