Macro to delete data based on criteria

[Hiport]

Hi, i have 2 worksheets in my workbook, "Raw_Data" and "Active_P'folios,
in the "Active_P'folios worksheet, in Col A starting at A3 is a list of p'folios which are active, and in the "Raw_Data" worksheet, starting in Col A3 is a list of p'folios which are active and non active (i.e there are some p'folios which are not in the "Active_P'folios" worksheet)

I need a macro to compare data from Col A3 onwards in "Raw_Data" worksheet to Col A3 onwards in "Active_P'folios" worksheet, and delete entire row of p'folios which are non active, i.e. p'folios which are not in "Active_P'folios worksheet

 

[Joe Was]

See if this is along the lines you need:

Sub DelBadRows()
'Sheet Module code, like: Sheet3.
'Find all the rows for which column "C" =
'a value in the "badList" and delete it.

Application.ScreenUpdating = False

For Each c In Sheets("Sheet3").Range("badList")

For Each r In Worksheets("Sheet4").UsedRange.Rows
If c.Value = xlNull Then GoTo myNext

If Worksheets("Sheet4").Cells(r.Row, 3) = c.Value Then
Worksheets("Sheet4").Cells(r.Row, 3).EntireRow.Delete
End If
Next r

myNext:
Next c
Application.ScreenUpdating = True
End Sub

 

[Hiport]

Hi

I tried your code, but its deleting a lot of p'folios then it should, basically what i need is Col A3 onwards in "Raw_Data" to compare against A3 onwards in "Active_P'folios" worksheet , if the pf'olios are not
on the "Active_P'folios" worksheet there then delete the p'folios in "Raw_Data" worksheet.

See if this is along the lines you need:

Sub DelBadRows()
'Sheet Module code, like: Sheet3.
'Find all the rows for which column "C" =
'a value in the "badList" and delete it.

Application.ScreenUpdating = False

For Each c In Sheets("Sheet3").Range("badList")

For Each r In Worksheets("Sheet4").UsedRange.Rows
If c.Value = xlNull Then GoTo myNext

If Worksheets("Sheet4").Cells(r.Row, 3) = c.Value Then
Worksheets("Sheet4").Cells(r.Row, 3).EntireRow.Delete
End If
Next r

myNext:
Next c
Application.ScreenUpdating = True
End Sub

 

[Hiport]

Your code is working the other way round, its getting rid of the p'folios i need and keeping th p'folios i dont need,

I will give you an example with one p'folio, now i know ZISZZP is not an active p'folio, so in my Active_P'folios worksheet this p'folio will not be listed in Col A, but in the Raw_Data worksheet ZISZZP this p'folio is listed, therefore i need your code to work the other way round, if ZISZZP is not in Active_P'folios worksheet the delete ZISZZP in "Raw_Data" worksheet,

the objective is to have the same p'folios in both Active_P'folios and Raw_Data

 

[Hiport]

just wondering if anyone has had a chance to look at the above

 

[Joe Was]

Change the Worksheets name in the code below to yours, the ranges should be OK:


Sub DelBadRows()
'Standard Module code, like: Module1.
Dim strRawVal$, strActiveVal$, strRAddr$
Dim lngLstRowA&, lngLstRowR&, myCount&, Cycle&
Dim Cell As Object, a As Object
Dim myRaw As Range, myActive As Range

'Application.ScreenUpdating = False

For Cycle = 1 To 5

With Worksheets("Active").UsedRange
lngLstRowA = .Rows.Count + .Row - 1
End With

With Worksheets("Raw").UsedRange
lngLstRowR = .Rows.Count + .Row - 1
End With

Worksheets("Active").Select
Set myActive = Worksheets("Active").Range(Cells(3, 1), Cells(lngLstRowA, 1))

Worksheets("Raw").Select
Set myRaw = Worksheets("Raw").Range(Cells(3, 1), Cells(lngLstRowR, 1))

For Each Cell In myRaw
strRAddr = Cell.Address
strRawVal = Cell.Value

For Each a In myActive
strActiveVal = a.Value
If strActiveVal = strRawVal Then myCount = myCount + 1 Else
Next a

If myCount = 0 Then _
Worksheets("Raw").Range(strRAddr).EntireRow.Delete
myCount = 0
Next Cell
Next Cycle

'Application.ScreenUpdating = True
End Sub