Seamus2626
asked on
Filter and Delete
Hi,
I need some code that will filter and delete rows bases on the last two columns
So if
FE = "CMB" & FF = "US"
THEN DELETE ENTIRE ROW
What i dont want is gaps in the data, so if that data existed in rows 94-98,i wouldnt want blank spaces there, im sure delete looks after that, but just making sure!
Thanks
Filter-and-Delete-EE.xlsx
I need some code that will filter and delete rows bases on the last two columns
So if
FE = "CMB" & FF = "US"
THEN DELETE ENTIRE ROW
What i dont want is gaps in the data, so if that data existed in rows 94-98,i wouldnt want blank spaces there, im sure delete looks after that, but just making sure!
Thanks
Filter-and-Delete-EE.xlsx
ASKER
Yes, i need this to form part of a macro which regularly does the task
Thanks
Thanks
This macro deletes all string patters in Column A
Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536" ).End(xlUp ))
SrchStr = InputBox("Please Enter A Search String")
Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536"
SrchStr = InputBox("Please Enter A Search String")
Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi,
Not for points.
Here's a slightly modified version of Harry's code with a check that there are some visible rows after the filter is applied. I've removed the ".select" lines, which relate to the rows being deleted, because it may make the code slightly faster too.
hth
Rob
Not for points.
Here's a slightly modified version of Harry's code with a check that there are some visible rows after the filter is applied. I've removed the ".select" lines, which relate to the rows being deleted, because it may make the code slightly faster too.
Option Explicit
Sub DeleteFEandFFRows_v2()
Dim WS As Worksheet, FltRng As Range, FESrcStr As String, FFSrcStr As String, DelFltRng As Range
Dim VisRows As Range 'visible rows
Set WS = ActiveSheet
With WS
Set FltRng = .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
Set DelFltRng = .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With
FESrcStr = InputBox("Please Enter Search String for Column FE")
If IsNull(FESrcStr) Then
MsgBox ("Invalid Search String Is Entered")
Exit Sub
End If
FFSrcStr = InputBox("Please Enter Search String for Column FF")
If IsNull(FFSrcStr) Then
MsgBox ("Invalid Search String Is Entered")
Exit Sub
End If
With FltRng
.AutoFilter
.AutoFilter Field:=161, Criteria1:=FESrcStr
.AutoFilter Field:=162, Criteria1:=FFSrcStr
'set a range variable to the visible rows after applying the filter, test that some rows exist & delete them
Set VisRows = DelFltRng.SpecialCells(xlCellTypeVisible).EntireRow
If Not VisRows Is Nothing Then
VisRows.Delete Shift:=xlUp
End If
.AutoFilter
End With
WS.Cells(1, 1).Select
Set VisRows = nothing
End Sub
hth
Rob
ASKER
Hi guys,
all looks good, but can i have the code without the search boxes, so it the code just runs without any user interaction
Thanks
all looks good, but can i have the code without the search boxes, so it the code just runs without any user interaction
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, perfect, that makes sense Rob.
Thanks
Thanks
Reg