Find and delete

[elricsia]

Hi all,

I need help to delete 12 (P:AB) columns on my data.

D10 = Key in range

P4:AB500 = data table
P=company name

First i need vba to help me find the D10 value first.

Second to delete entire row (P:AB) which company name match with D10.

Thank you.

[:) Sixthsense :)]

Where will be the company name?

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[elricsia]

Sorry. Didn't mention it. Every time i will key in the company name on D10.

D10 = to key in company name

P4:AB500 = data table
P=company name

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[elricsia]

yes! this is what exactly i want! Thank you!

[Vikas_Gautam]

Try this by adding a button besides D10 Range and linking a Macro holding following code.

Sub DelRow()
 Dim SrchIn as Range, Fnd as Range, DelRng as Range
 Set SrchIn =  Columns("P")
 
 With SrchIn
  set Fnd = .Find([D10]) 
  fAdd = Fnd.Address
  
  If fnd is Nothing then Msgbox "No Match is Found", vbinformation : End
  
  Do
   set DelRng =  Union(DelRng, Fnd)
   Set Fnd = .findNext(Fnd)  
  loop while not Fnd is nothing and fAdd <> Fnd.Address 
 
 End with
 DelRng.Entirerow.delete xlup
 Msgbox "Finished", vbinformation
End Sub 

if you want to make it automatic then you can use Worksheet Change Event
Here is the code:-

Private Sub Worksheet_Change(ByVal Target as Range)
 if Target.address <> "$D$10" then End
 Dim SrchIn as Range, Fnd as Range, DelRng as Range
 Set SrchIn =  Columns("P")
 
 With SrchIn
  set Fnd = .Find([D10]) 
  fAdd = Fnd.Address
  
  If fnd is Nothing then Msgbox "No Match is Found", vbinformation : End
  
  Do
   set DelRng =  Union(DelRng, Fnd)
   Set Fnd = .findNext(Fnd)  
  loop while not Fnd is nothing and fAdd <> Fnd.Address 
 
 End with
 DelRng.Entirerow.delete xlup
 Msgbox "Finished", vbinformation
End Sub 

Paste the above code in the Sheet module.