Hello,
I want to create a macro which can search and match values in a particular column and filter out data based on it . The values that need to be searched would be present in another sheet.The rest data that does not match the criteria are to be deleted(entire rows)
Could you pl send a xl file and show what you want ?
Here's a dummy file. Here Data on sheet 1 has to be filtered out using data on sheet 2. That is find all the rows that have the data present in sheet 2 and delete the rest
I've added a DeleteMapRows macro to your sample data which is one way to to do what I think you are trying to achieve
Hello Aroy,
Here's another method:-
Sub Test()
Dim ar As Variant
ar = Sheet2.Range("A2", Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For i = 1 To UBound(ar, 1)
With Sheet1.[A1].CurrentRegion
.AutoFilter 2, ar(i, 1)
.Offset(1).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
.AutoFilter
End With
Next i
Sheet1.UsedRange.Offset(1).Delete
Sheet3.[A2].CurrentRegion.Copy Sheet1.[A2]
Sheet3.Cells.Clear
Application.ScreenUpdating = True
End SubThe code uses a helper sheet (Sheet3) to temporarily store the required data for an instant. It is then cleared.
I hope that this helps.
Cheerio,
vcoolio.
Hi Aroy,
Pl try this code. The file is also attached. You can delete the sheet 'Data'
Sub DeleNonMatchingRows()
'Deletes the rows Sh1 B column with blanks and non matching records (with sh2 A column)
Dim lngCounter As Long, lngRows As Long, lngRows2 As Long
Dim ws As Worksheet
Dim strSearch As String
Dim blnDeleted As Boolean
Dim rng As Range
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
lngRows = ws.Range("A" & Rows.Count).End(xlUp).Row
lngRows2 = Worksheets("Sheet2").Range("A1").End(xlDown).Row
Set rng = Worksheets("Sheet2").Range("A2:A" & lngRows2)
For lngCounter = lngRows To 2 Step -1
If ws.Range("B" & lngCounter).Value <> "" Then
strSearch = ws.Range("B" & lngCounter).Value
If rng.Find(what:=strSearch, LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
ws.Rows(lngCounter).EntireRow.Delete
blnDeleted = True
End If
Else
ws.Rows(lngCounter).EntireRow.Delete
blnDeleted = True
End If
Next lngCounter
If blnDeleted Then
MsgBox "Rows with blanks and non matching values deleted", vbInformation + vbOKOnly, "Rows Deleted"
Else
MsgBox "No Rows to delete", vbInformation + vbOKOnly, "No Deletion.."
End If
Application.ScreenUpdating = True
End SubThnak You fro your response guys. I am trying out @B Rama Krishna's code first..just needed a little tweak..i want to a filter first for column A in sheet 1..say the values are Data,Data 1, Data2 and I want to filter out ranges having data and data 2 as cell value and then perform the mentioned code above on that filtered out data...can you help [USER="334446"]B Rama Krishna[/USER] ?
Hi,
Pl let me know if my understanding is correct. For the cell values in Col A of sheet 1
a) Retain rows with only 'Data', 'Date1' and 'Data2'
b) From this data remove rows with 'Data' and 'Data2' (that leaves rows with only 'Data1')
c) then perform the matching with Col A of Sheet 2 and delete non-matching rows
In any case would appreciate if you could send an xl sheet and explain what needs to be done, so that I can work on the same.
Hello Rama,
Thank you once again.
I sorted that out. What I wanted to do was to first filter out data from sheet 1 and then delete data from the filtered out result using the mappings.
Good!
Don’t have an account yet? Register yourself now and be a part of our community!