Search & Delete

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Hi anyone,

How could I write a code that would search for the value in cell K10 of the active sheet from column B of Sheet 2 & delete the entire row if the row has the value in cell K10 of the active sheet.

Any help on this would be kindly appreciated.

Thanks in advance.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
Code:
Sub delete_row()
Dim as1 As Worksheet: Set as1 = ActiveSheet
looking = as1.Range("K10").Value
lr = as1.Cells(Rows.Count, 2).End(xlUp).Row
    For x = 2 To lr
        finding = as1.Cells(x, 2).Value
        If finding = looking Then
            as1.Rows(x & ":" & x).Delete.EntireRow
            x = x - 1
        End If
    Next x
End Sub
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Thanks for the help. But the code does not seem to work. Meaning, it does not search for the value in cell K10 of the active sheet from column B of "Sheet 2" & delete the entire row if the row has the value in cell K10 of the "active sheet".

Why is this?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
adamsm,

Sample raw data in worksheet Sheet2:


Excel 2007
ABCDEF
1A1748903C1D1E1F1
2A2748904C2D2E2F2
3A3748905C3D3E3F3
4A4748906C4D4E4F4
5A5748907C5D5E5F5
6A6748908C6D6E6F6
7
Sheet2


Sample raw data in the active worksheet:


Excel 2007
K
10748905
Sheet1


After the macro in worksheet Sheet2:


Excel 2007
ABCDEF
1A1748903C1D1E1F1
2A2748904C2D2E2F2
3A4748906C4D4E4F4
4A5748907C5D5E5F5
5A6748908C6D6E6F6
6
7
Sheet2


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub FindW1K10inW2colB_DeleteRow()
' hiker95, 01/07/2014
' http://www.mrexcel.com/forum/excel-questions/748905-search-delete.html
Dim rng As Range, fk10rng As Range
Set rng = Sheets("Sheet2").Columns(2)
With ActiveSheet
  If .Range("K10") = "" Then
    MsgBox "The cell K10 in the active sheet is blank - macro terminated!"
  Else
    Set fk10rng = rng.Find(.Range("K10").Value, LookAt:=xlWhole)
    If Not fk10rng Is Nothing Then
      Sheets("Sheet2").Rows(fk10rng.Row).Delete
    Else
      MsgBox "The value of cell K10 = " & .Range("K10").Value & " was NOT found in Sheet2 column B - macro terminated!"
    End If
  End If
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindW1K10inW2colB_DeleteRow macro.
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Thanks for the help & the code works now. I do have the value in K10 of the active sheet in in multiple rows of column B. The code now deletes a row at a time.

How could I extend the code further so that it would delete all the rows of column B that contains the value on cell K10?

Any help on this would be kindly appreciated.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
adamsm,

When you respond to your helper, please use their site ID/username/handle. This way we know who should respond.

I do have the value in K10 of the active sheet in in multiple rows of column B.

If you had mentioned that from the beginning we would already be finished.

Be back in a little while.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
adamsm,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub FindW1K10inW2colB_DeleteRowV2()
' hiker95, 01/07/2014
' http://www.mrexcel.com/forum/excel-questions/748905-search-delete.html
Dim rng As Range, fk10rng As Range, n As Long, i As Long
Set rng = Sheets("Sheet2").Columns(2)
With ActiveSheet
  If .Range("K10") = "" Then
    MsgBox "The cell K10 in the active sheet is blank - macro terminated!"
  Else
    Set fk10rng = rng.Find(.Range("K10").Value, LookAt:=xlWhole)
    If Not fk10rng Is Nothing Then
      n = Application.CountIf(Sheets("Sheet2").Columns(2), .Range("K10").Value)
      If n = 1 Then
        Sheets("Sheet2").Rows(fk10rng.Row).Delete
      ElseIf n > 1 Then
        For i = 1 To n Step 1
          Set fk10rng = rng.Find(.Range("K10").Value, LookAt:=xlWhole)
          Sheets("Sheet2").Rows(fk10rng.Row).Delete
        Next i
      End If
    Else
      MsgBox "The value of cell K10 = " & .Range("K10").Value & " was NOT found in Sheet2 column B - macro terminated!"
    End If
  End If
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindW1K10inW2colB_DeleteRowV2 macro.
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Thanks for the help hiker95. The code worked & I do really appreciate your help. Meanwhile, sorry for the misunderstanding.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
adamsm,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 

Forum statistics

Threads
1,172,238
Messages
5,879,866
Members
433,461
Latest member
Confusedexcelhelp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top