=AYUDA=COMO ELIMINAR

[yra_vj]

Hola que tal
Estoy tratando de hacer una macro que elimine filas completas atraves de una condicion, solo que en mi lista existen filas vacias, y no me doy idea de como hacer para que solo borre las filas que no necesito, les mostrare un ejemplo de como esta mi informacion:

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" > <tr valign="top"> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Tipo</nobr></font></th> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Cantidad</nobr></font></th> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Precio</nobr></font></th> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>456900</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>809777</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>567</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>5000</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>KG</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>34</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>456</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>PZA</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>567</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>56789</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>678</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>678900</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>PZA</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>777</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>89770</nobr></font></td> </tr></table>

Yo solo quiero quedarme con todo lo que sea = a ENB, lo demas no me sirve, este es el codigo que estoy usando, pero no me doy idea de como saltarme las filas para que continue borrando lo que no me sirve:

Sub Eliminar()
  ActiveCell.Offset(1, 0).Range("A1").Select
    While ActiveCell.Value <> ""
        If ActiveCell.Value = "PZA" Or "KG" Then
           Selection.Delete Shift:=xlUp
        Else
          ActiveCell.Offset(1, 0).Range("A1").Select
        End If
    Wend
End Sub

Porfavor cualquier idea sera muy agradecida!!

<hr />editado por Greg para poner los datos en una tabla y el VBA dentro de etiquetas de "code"

 

[yra_vj]

mis datos ahora estan asi:

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" align="left"> <tr valign="top"> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>No.</nobr></font></th> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Cantidad</nobr></font></th> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Precio</nobr></font></th> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Tipo</nobr></font></th> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>1</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>456900</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>809777</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>2</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>567</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>5000</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>3</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>34</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>456</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>KG</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>4</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>567</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>56789</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>PZA</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>5</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>678</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>678900</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>ENB</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>6</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>777</nobr></font></td> <td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>89770</nobr></font></td> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>PZA</nobr></font></td> </tr></table>

 

[Greg Truby]

Tiene que cambiar la línea

    rngCorriente.AutoFilter Field:=1, Criteria1:="<>ENB"

a

    rngCorriente.AutoFilter Field:=4, Criteria1:="<>ENB"

y la línea

        .Sort Key1:=Range("A1"), _

a

        .Sort Key1:=Range("D1"), _

Entonces al final se ve asi:

Sub BorrarNoIguales()
    Dim rngCorriente As Range, rngColumna As Range, _
        rngNoENB As Range, rngUltima As Range
    
    Set rngUltima = udfLastCell(ActiveSheet)
    Set rngCorriente = Range(Range("A1"), rngUltima)
    Set rngColumna = rngCorriente.Columns(1)
    With rngColumna
        Set rngColumna = .Offset(1).Resize(.Rows.Count - 1)
    End With
    
    rngCorriente.AutoFilter Field:=4, Criteria1:="<>ENB"
    
    Set rngNoENB = Nothing
    On Error Resume Next
    Set rngNoENB = rngColumna.SpecialCells(xlCellTypeVisible)
    If Err.Number <> 0 Then
        rngCorriente.AutoFilter
        Exit Sub
    End If
    
    rngNoENB.ClearContents
    With rngCorriente
        .AutoFilter
        .Sort Key1:=Range("D1"), _
              Order1:=xlAscending, _
              Header:=xlYes, _
              Orientation:=xlTopToBottom
    End With
    rngColumna.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Function udfLastCell(ws As Worksheet) As Range
    Dim LastRow&, LastCol%
    LastRow = 1: LastCol = 1
    ' Error-handling is here in case there is not any
    ' data in the worksheet
    On Error Resume Next
    With ws
        ' Find the real last row
        LastRow = .Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
        ' Find the real last column
        LastCol = .Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByColumns).Column
    End With
    Set udfLastCell = ws.Cells(LastRow, LastCol)
End Function

 

[yra_vj]

GRACIAS!!

Muchas gracias, esto resuelve mi problema, ahora solo voy a complementar esta macro con unas fomrulas, muchas gracias por la ayuda!!