Ignoring Rows When Extracting Data From One Worksheet To Another

[Jim J.]

Is there some way to ignore data in certain rows, while extracting data from
others, while not leaving any empty rows at the extracted level? Here’s what
I want to do:
Worksheet #1 has data in rows (which I copy and paste as “text strings†from
a report, because the “Text Import Wizard†in Excel can’t deal with all the
clutter from the original report). A partial example of the report data
would be as follows:
05/05/2006 873318 0030 Rlsd PART102-002 03/09
Cbl 10/2 Cable Blk 1
Totals: 5.25
0.5

05/16/2006 010796 0020 Plnd PART109C-002 04/18
Cbl 10/1 Cable 80c B
Totals:
0.3333 1.0

05/17/2006 990778 0020 Rlsd PART284-008 05/09
Cbl 28/4 Cable 75c
Blk 7X
988467 0020 Strt PART284-002 05/08
Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09
Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08
Cbl 26/5 M Cable 80
Totals:
7.3333 2.6
The only rows that I want to extract data from are ones that have a 6-digit
Order Number (ex: 873318, 010796, 990778, 998467, etc.). IF the row has an
Order Number, I will extract data from that row, as well as the following
row. If a row is blank, or has a “Totals:†entry, I want to ignore those
rows completely. So once I extract the data to Worksheet #2, it should look
like this:
873318 0030 Rlsd PART102-002 03/09 Cbl 10/2 Cable Blk 1
010796 0020 Plnd PART109C-002 04/18 Cbl 10/1 Cable 80c B
990778 0020 Rlsd PART284-008 05/09 Cbl 28/4 Cable 75c Blk 7X
988467 0020 Strt PART284-002 05/08 Cbl 28/4 M Cable 10
003039 0020 Strt PART223-032 05/09 Cbl 22/3 105C YE
978250 0030 Strt PART265-001 05/08 Cbl 26/5 M Cable 80
So, in the end, out of the 17 rows that I started with, I end up with only 6
rows.
PS By the way, I know how to extract the data for the various columns from
the various text strings, so that isn’t an issue.

 

[Otto Moehrbach]

The following macro will do that for you. I assumed your Order Numbers are
in Column B and each subsequent entry in subsequent columns, ending with
Column F.
This macro will look at each cell in Column B. When it finds an entry that
is 6 characters long, it will copy Columns B:F of that row and paste it into
a sheet named "Two", starting in row 2, in Column B. It will then take the
entry (in the first sheet) in the next row in Column F and copy that cell
and paste it to the same row in sheet "Two" in Column G.
It will then look at the next cell in Column B of the first sheet. Post
back if this doesn't do what you want. HTH Otto
Sub ShuffleData()
Dim RngB As Range
Dim Dest As Range
Dim i As Range
Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
With Sheets("Two")
Set Dest = .Range("B2")
For Each i In RngB
If Len(i.Value) = 6 Then
i.Resize(, 5).Copy Dest
i.Offset(1, 4).Copy Dest.Offset(, 5)
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub

 

[Jim J.]

Otto,
This might work, but I have insufficient "macro" knowledge to try this out.
A suggestion, please, regarding how I could learn more about macros so I can
try this out.
Thanks,
Jim J.