Simple- Find and Delete Row

[Ryokan]

I have a combo box in a userform and i need to find a match in a specific column (A) of the worksheet “Inventario” and delete the complete row.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>Thank You </o>

 

[Joe4]

If you start off by using the Macro Recorder, and record yourself using the Find feature, and then deleting the row, that will give you most of the code you need to do this. Then, you can go in and edit the code to clean it up to make it more dynamic.

Why not give it a shot, see how far you get, and then post your results back here if you need help amending that code to get it to work exactly the way you like?

 

[Ryokan]

Hi, got this

Sub Macro1()
'
' Macro1 Macro
' Macro grabada el 26/07/2011 por user
'

'
Rows("16:16").Select
Selection.Delete Shift:=xlUp
End Sub


doesnt really help me at all

 

[Joe4]

You need to turn on the Macro Recorder BEFORE you use the Find command to get that code. So you want to record yourself using the Find method in Excel and then deleting the row.

Also, if you use this Forum's Search functionality, you will be able to find many threads which show ways you can do this (using various methods). Just seach on "find delete macro" (set to titles only) and you will find lots of matches. Here are but a few:
http://www.mrexcel.com/forum/showthread.php?t=560940
http://www.mrexcel.com/forum/showthread.php?t=479547

If you look at these, and still are stuck, let me know.

 

[Ryokan]

Im trying but dont get any results, i got this:

Dim rFound As Range, Str As String
Dim wsss As Worksheet
Set wsss = Worksheets("Inventario de Anillos-Cilindros")
Application.ScreenUpdating = False
With wsss
Str = Me.cboElCodCilindro.Value
Do
Set rFound = Cells.Find(Str, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
If Not rFound Is Nothing Then Rows(rFound.Row).EntireRow.Delete xlShiftUp
Loop Until rFound Is Nothing
End With
Application.ScreenUpdating = True

This is just a part of a big procedure that fill cells in other worksheet, all is working right but doesnt delete the specified row.

"Me.cboElCodCilindro.Value" is the info on the combo box (cboElCodCilindro)

 

[Joe4]

Since I do not have a Form/Combo Box, I changed one line of your code so I could test it.

I changed:
Str = Me.cboElCodCilindro.Value
to
Str = InputBox("Enter value to find")
which brings up an Input Box I can enter my search value into.

I tried it and it worked, successfully deleting all rows that had that entry.

To make sure you are correctly capturing the value of your Combo Box, try returning a message box with its value right after you set it, i.e. after:
Str = Me.cboElCodCilindro.Value
add this line:
MsgBox Str
Verify it is returning the value you want it to seach for/delete.

Also, another trick is to resize your VBA Editor to about one-quarter the size of your screen so you can see your worksheet and VBA at the same time. Highlight the first line of your VBA code and click F8. This will process the first line of the code. Keep pressing F8 to process your code one line at a time, and then you can see what your code is actually doing in the background. This allows you to follow along and to make sure each step appears to be doing what you expect, and identify potential issues.

 

[Ryokan]

Hi joe4,

First of all thank you very much for your help, i tried what you told me, has i can see it only works if the active worksheet is the one where the text to delete is in, when i activate the macro from the worksheet wheres the button is doesnt do anything , im really confuced right now; how i can make this work from a different sheet or wheres the issue here?

Thanks

 

[Joe4]

Assuming you are working from a standard module, simply activate your sheet before entering your WITH statement, i.e.

wsss.Activate

 

[Ryokan]

Yess finally, Thank You Joe4