Extracting unwanted rows from a excel workbook

[thales]

Hello,

I have the following problem in the examle below which start on cell a1 to the end (last row will be blank):
It's a list imported from a pdf file in which I just want to keep the rows maked in yellow( originaly they are not in yellow.I marked just to highlight the rows that I want to keep).
I can see that it starts in A1 (the info that I want to keep) and has 13 rows that I want to delete under until the next row that I want to keep.
At the end I just want to have the raws in yellow and it would be like this:
<TABLE style="WIDTH: 281pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=374 x:str><COLGROUP><COL style="WIDTH: 281pt; mso-width-source: userset; mso-width-alt: 15957" width=374><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; WIDTH: 281pt; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=16 width=374>1031 GEWURZTRAMINER</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=16>1035 POULLY FUME LES</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=16>1040 QUINTA DA BACALHOA
.... and so on
The code has to be flexible to choose the number of rows to be deleted. In this case 13( number of rows between the two yellow rows that I want to keep)
Also, after having removed all the unwanted rows I will have to break the information on each cell into two columns the first with the numeric code and the second with the name( ex: a1=1031 and b1= GEWURZTRAMINER)

Thanks for the help


</TD></TR></TBODY></TABLE>
example of the rows that I want to work on.

<TABLE style="WIDTH: 281pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=374 x:str><COLGROUP><COL style="WIDTH: 281pt; mso-width-source: userset; mso-width-alt: 15957" width=374><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; WIDTH: 281pt; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=16 width=374>1031 GEWURZTRAMINER</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid estq: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Grupo:FRANÇA</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço custo: 68,88</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Vendável</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Estocável</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid compra: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Tipo:</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>um para um</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço lista: 0,00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>DInativo</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Serviço</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid venda: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Pnl. contas: saidas operacionais » compra de produtos diretos » bebidas » vinhos</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=16>1035 POULLY FUME LES</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid estq: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Grupo:FRANÇA</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço custo: 0,00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Vendável</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Estocável</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid compra: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Tipo:</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>um para um</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço lista: 0,00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>DInativo</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Serviço</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid venda: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Pnl. contas: saidas operacionais » compra de produtos diretos » bebidas » vinhos</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=16>1040 QUINTA DA BACALHOA</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid estq: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Grupo:PORTUGAL</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço custo: 87,33</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Vendável</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Estocável</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid compra: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Tipo:</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>um para um</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Preço lista: 0,00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>DInativo</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=16>0Serviço</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Unid venda: GF</TD></TR><TR style="HEIGHT: 9.95pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 9.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=13>Pnl. contas: saidas operacionais » compra de produtos diretos » bebidas » vinhos</TD></TR></TBODY></TABLE>

 

[Trebor76]

Hi thales,

Welcome to MrExcel!!

Try this (initially on a copy of your data in case the results are not as expected):

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=632101

    Dim lngMyRow As Long
    Dim strCellValue As String
    
    Application.ScreenUpdating = False

    For lngMyRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If IsNumeric(Left(Range("A" & lngMyRow), 4)) = True Then
            strCellValue = Range("A" & lngMyRow)
            Range("A" & lngMyRow) = Left(strCellValue, 4)
            Range("B" & lngMyRow) = Trim(Replace(strCellValue, Left(strCellValue, 4), ""))
        Else
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Rick Rothstein]

Assuming your data starts in A1, is located completely in Column A and that the data you want to keep is separated from each other by 13 rows of other data, then this code should do what you want and, I would think, quite quickly...

Sub KeepRowsStartingWithNumbers()
  Dim X As Long, LastRow As Long, Parts() As String, Data As Variant, Keepers As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Data = Range("A1:A" & LastRow).Value
  ReDim Keepers(1 To LastRow \ 14, 1 To 2)
  For X = 0 To UBound(Keepers) - 1
    Parts = Split(Data(1 + 14 * X, 1), " ", 2)
    Keepers(X + 1, 1) = Parts(0)
    Keepers(X + 1, 2) = Parts(1)
  Next
  Columns("A:B").Clear
  Range("A1:B" & UBound(Keepers)) = Keepers
End Sub

 

[Trebor76]

Hi Rick,

Nice one

Based on the same assumptions you made, I also thought of this...

Sub Macro2()

    'http://www.mrexcel.com/forum/showthread.php?t=632101

    Dim lngActiveRow As Long, _
        lngPasteRow As Long
    Dim strCellValue As String
    
    Application.ScreenUpdating = False
    
    Columns("A:B").Insert

    For lngActiveRow = 1 To Cells(Cells.Rows.Count, "C").End(xlUp).Row Step 14
        If lngPasteRow = 0 Then
            lngPasteRow = 1
        Else
            lngPasteRow = lngPasteRow + 1
        End If
        strCellValue = Range("C" & lngActiveRow)
        Range("A" & lngPasteRow) = Left(strCellValue, 4)
        Range("B" & lngPasteRow) = Trim(Replace(strCellValue, Left(strCellValue, 4), ""))
    Next lngActiveRow
    
    Columns("C").Clear
    
    Application.ScreenUpdating = True

End Sub

...which though more efficient than my first attempt is still probably not as quick as your nifty array approach.

The OP is the winner here I say

Cheers,

Robert

 

[mirabeau]

well, if you'd like one to do it somewhat more quite quickly...

Sub bssb()
t = Timer
Dim a, b(), k&, i&, n&, x&
a = Range("A1", Cells(Rows.Count, 1).End(3))
n = UBound(a, 1)
ReDim b(1 To Int(n / 14) + 1, 1 To 2)
For i = 1 To n Step 14
    k = k + 1
    x = InStr(a(i, 1), " ")
    b(k, 1) = Left(a(i, 1), x)
    b(k, 2) = Right(a(i, 1), Len(a(i, 1)) - x)
Next i
Range("A1").Resize(n).Clear
Range("A1").Resize(k, 2) = b
Debug.Print Round(Timer - t, 3) & "  mirabeau"
End Sub

 

[Trebor76]

The OP is the winner here I say

Ditto!!

Just one question mirabeau. As I always use Option Explicit, what variable would one assign to t ?

Also, cheers to Rick for becoming a MrExcel MVP

Robert

 

[mirabeau]

Ditto!!

Just one question mirabeau. As I always use Option Explicit, what variable would one assign to t ?

Also, cheers to Rick for becoming a MrExcel MVP

Robert

As it is it defaults as variant. I only put it in as an afterthought for timing. I wasn't that serious about that code anyway. Rick's is fine, as are your's.
If you want to use option explicit you can call t variant or single or double.

 

[Trebor76]

Thanks mirabeau

 

[thales]

thanks for your code but I need to know how to run it. How do I run this code to test it. Can you help me on that. I want, it for example, assign to "ctrl-q" to run the code on my worksheet.

 

[Rick Rothstein]

thanks for your code but I need to know how to run it. How do I run this code to test it. Can you help me on that. I want, it for example, assign to "ctrl-q" to run the code on my worksheet.

INSTALL MACROs
-----------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name you want from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

As for you short-cut key question... when you press ALT+F8 and select the macro name from the list... before you click the Run button, click the "Options..." but and put the letter "q" in the indicated field and then click the OK button.