Delete row with column value = ...

[jsp_1951]

How can I delete all rows of a spreadsheet that have a specified value or string in a named column

Column header "NAME" may not be in the same column in every spreadsheet

I'm not a VBA programmer but I would like to insert this function in a MACO routine I have recorded.

[Chippy]

Try this on a copy of your workbook. Change the constants as required. These define the column header and column value which determines whether the row is deleted.

Please Login or Register  to view this content.

[jsp_1951]

Chippy,

Thanks, the code works as I requested. How can I modify to delete rows if a column's numeric value is "greater than" or "less than" a value? Currently I use your code to operate on columns with values of 0-1, created by a simple IF function in a macro. Conditional values for your row delete would eliminate this interim step.

What would really make things simple would be an InputBox pop-up that asks;

- "Column Name"
- "Condition" (<,>,=)
- "Value"

Upon "OK" the rows are deleted. I could then make progressively stringent cutoffs without alot of macro editing.

Thanks,
Jim

[Chippy]

Try this (sorry for not using code tags - see below *):

Sub Find_Column_Header_Delete_Rows3()

Dim ws As Worksheet
Dim ColumnHeader As Range
Dim lastRow As Long, row As Long
Dim userInput As String
Dim p As Integer

userInput = InputBox("Enter Column Name, Condition (<,>,=) and Value.")

If userInput <> "" Then

userInput = Replace(userInput, " ", "")
p = InStr(userInput, "<")
If p = 0 Then p = InStr(userInput, ">")
If p = 0 Then p = InStr(userInput, "=")

If p >= 2 Then

For Each ws In Worksheets

Set ColumnHeader = ws.Rows(1).Find(What:=Left(userInput, p - 1), After:=ws.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not ColumnHeader Is Nothing Then
lastRow = ws.Cells(Rows.Count, ColumnHeader.Column).End(xlUp).row
For row = lastRow To 2 Step -1
If Evaluate(Chr(34) & ws.Cells(row, ColumnHeader.Column).Text & Chr(34) & Mid(userInput, p, 1) & Chr(34) & Mid(userInput, p + 1) & Chr(34)) Then
ws.Rows(row).Delete
End If
Next
End If

Next

End If
End If

End Sub


Examples of the expected input by the user (spaces are removed from the input):

NAME>1
NAME = abc


* Mods: I've been trying to post this code for the last 2 days. For some reason, when the code is surrounded by CODE tags (also tried HTML and PHP tags) and I preview or submit the reply, my browser (IE8 and Firefox) displays a blank white page and the browser is then unable to open www.excelforum.com - Firefox says 'Firefox can't establish a connection to the server at www.excelforum.com.' - until the next day. This is the current situation and I'm having to use a proxy server to access www.excelforum.com and post this reply. I suspect the problem is something to do with the angle brackets < > in the code.