My spreadsheet has "sections":

A1 thru N18513
O1 thru AC4841

If the contents of any cell in Col O and any cell in Col A are the
same, how can I keep the contents of the cells in both matching
"sections" (i.e., the cells in A thru N and the cells in O thru AC)?

on 1/29/2011, gcotterl supposed :
My spreadsheet has "sections":

A1 thru N18513
O1 thru AC4841

If the contents of any cell in Col O and any cell in Col A are the
same, how can I keep the contents of the cells in both matching
"sections" (i.e., the cells in A thru N and the cells in O thru AC)?

I'm guessing that what you want is to have cells A that have a match in
cells O to be lined up in the same row. Or are you asking something
different? You really need to be more precise about exactly what it is
you want.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

gcotterl laid this down on his screen :
Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

Hi Gary,
Can you send me the file?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

gcotterl has brought this to us :
Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 29, 4:48*pm, GS wrote:
gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.

On Jan 29, 6:37*pm, gcotterl wrote:
On Jan 29, 4:48*pm, GS wrote:





gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.- Hide quoted text -

- Show quoted text -

By the way, I'm using Microsoft Office Excel 2007.

On Jan 29, 4:48*pm, GS wrote:
gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I don't know what you mean by "locate (row position) 2nd section value
in $A:$A, cut the cells in 2nd section, insert (shift down) cut cells
in 2nd section at found row
position".

gcotterl expressed precisely :
On Jan 29, 4:48*pm, GS wrote:
gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.

Sorting the 2nd section just sorts its data. Since the first section
has more rows, the matches will not end up on the same row. The sort
result would be what you have now.

Also, lining up 2nd section dupes with 1st section rows means there
will be blanks in 2nd section.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

gcotterl wrote :
On Jan 29, 4:48*pm, GS wrote:
gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I don't know what you mean by "locate (row position) 2nd section value
in $A:$A, cut the cells in 2nd section, insert (shift down) cut cells
in 2nd section at found row
position".

You did say you want matching data in 2nd section to be on same row as
match in 1st section. That means the data in 2nd section has to be
repositioned to the same row as match in 1st section, right?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 29, 7:37*pm, GS wrote:
gcotterl wrote :





On Jan 29, 4:48*pm, GS wrote:
gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. *(I tried describing this concept several ways
but *couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I don't know what you mean by "locate (row position) 2nd section value
in $A:$A, cut the cells in 2nd section, insert (shift down) cut cells
in 2nd section at found row
position".

You did say you want matching data in 2nd section to be on same row as
match in 1st section. That means the data in 2nd section has to be
repositioned to the same row as match in 1st section, right?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Right.

Are you suggesting that I should switch the two sections so:

Section 2 is in Cols A thru O
Section 1 in Cols P thru AC

If not, I don't understand your directions.

I've simplified my spreadsheet. It now has only two columns (A1 thru
A4841 and B1 thru B18513).

My question is: How do I eliminate the cells in Col B whose 19 left-
most characters do NOT match the 19 left-most characters in the cells
in Col A?

gcotterl laid this down on his screen :
Are you suggesting that I should switch the two sections so:

Section 2 is in Cols A thru O
Section 1 in Cols P thru AC

If not, I don't understand your directions.

No! The arrangement is fine as is.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

on 1/30/2011, gcotterl supposed :
I've simplified my spreadsheet. It now has only two columns (A1 thru
A4841 and B1 thru B18513).

My question is: How do I eliminate the cells in Col B whose 19 left-
most characters do NOT match the 19 left-most characters in the cells
in Col A?

Finally! We arive at what it is that you want (remove records).
Hooray!<g

So then, is what you're saying is you want to end up with a list of
only matches where all non-matching records are remove from both
sections? (resulting with both sections having the same number of rows)

OR

Do you want to remove non-matches from Section1 (A1:A18513) only and
somehow align the matches with Section2?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I want to end up with a list of only matches where all non-matching
records are removed from both sections? (resulting with both sections
having the same number of rows)

OR...
Are you looking for a solution to merely identify and highlight matches
so you can easily find them and view the data? This is what I did to
locate the matches in both sections (using CF). To view the matches
together I split the window vertically and scrolled either or both
panes to align whichever record I wanted.

OR...
In the case of a list of matches only, do you want the list put on a
separate worksheet so the original list remains intact?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

gcotterl laid this down on his screen :
I want to end up with a list of only matches where all non-matching
records are removed from both sections? (resulting with both sections
having the same number of rows)

Ok. See my other post about how/where to put the list.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

It'd be best to put the list of only the matches on a separate
worksheet.

It looks like I have no other option than to MANUALLY move each cell
in Col A so it lines up with the matching cell in the Col B.

gcotterl wrote :
It looks like I have no other option than to MANUALLY move each cell
in Col A so it lines up with the matching cell in the Col B.

No, you won't have to do that. I'm working on code to put all matches
on a new sheet but I've been interupted by a meeting. I'll resume later
on and post the code for you to copy into a standard module.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Here's what works for me. It takes a while to go through each cell in
colA, but takes less than a minute to process all 18513 entries.

Sub FindMatches()
Dim wksSource As Worksheet, wksTarget As Worksheet
Dim vVal As Variant, rng As Range
Dim lSect1Cols As Long, lSect2Cols As Long
Dim lSect1Rows As Long, lSect2Rows As Long
Dim lNextRow As Long, i As Long

'Hide screen activity
Application.ScreenUpdating = False

Set wksSource = ActiveWorkbook.ActiveSheet
Set wksTarget = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
wksTarget.Name = wksSource.Name & "_2"

With wksSource
lSect1Cols = .Range("$A$1:$N$1").Columns.Count
lSect2Cols = .Range("$O$1:$AC$1").Columns.Count
lSect1Rows = .Range("$A$1").End(xlDown).Row
' lSect2Rows = .Range("$O$1").End(xlDown).Row

For i = 1 To lSect1Rows
vVal = .Cells(i, 1).Value
Set rng = .Range("$O:$O").Find(what:=vVal)
If Not rng Is Nothing Then '//we have a match
Application.StatusBar = "Found match for " & vVal
lNextRow = lNextRow + 1
.Cells(i, 1).Resize(1, lSect1Cols).Copy _
Destination:=wksTarget.Cells(lNextRow, 1)
rng.Resize(1, lSect2Cols).Copy _
Destination:=wksTarget.Cells(lNextRow, lSect1Cols + 1)
End If
Next
End With
With wksTarget
.UsedRange.EntireColumn.AutoFit: .Activate
End With
Application.StatusBar = "" '//reset
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 30, 1:34*pm, GS wrote:
Here's what works for me. It takes a while to go through each cell in
colA, but takes less than a minute to process all 18513 entries.

Sub FindMatches()
* Dim wksSource As Worksheet, wksTarget As Worksheet
* Dim vVal As Variant, rng As Range
* Dim lSect1Cols As Long, lSect2Cols As Long
* Dim lSect1Rows As Long, lSect2Rows As Long
* Dim lNextRow As Long, i As Long

* 'Hide screen activity
* Application.ScreenUpdating = False

* Set wksSource = ActiveWorkbook.ActiveSheet
* Set wksTarget = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
* wksTarget.Name = wksSource.Name & "_2"

* With wksSource
* * lSect1Cols = .Range("$A$1:$N$1").Columns.Count
* * lSect2Cols = .Range("$O$1:$AC$1").Columns.Count
* * lSect1Rows = .Range("$A$1").End(xlDown).Row
' * *lSect2Rows = .Range("$O$1").End(xlDown).Row

* * For i = 1 To lSect1Rows
* * * vVal = .Cells(i, 1).Value
* * * Set rng = .Range("$O:$O").Find(what:=vVal)
* * * If Not rng Is Nothing Then '//we have a match
* * * * Application.StatusBar = "Found match for " & vVal
* * * * lNextRow = lNextRow + 1
* * * * .Cells(i, 1).Resize(1, lSect1Cols).Copy _
* * * * * * Destination:=wksTarget.Cells(lNextRow, 1)
* * * * rng.Resize(1, lSect2Cols).Copy _
* * * * * * Destination:=wksTarget.Cells(lNextRow, lSect1Cols + 1)
* * * End If
* * Next
* End With
* With wksTarget
* * .UsedRange.EntireColumn.AutoFit: .Activate
* End With
* Application.StatusBar = "" '//reset
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I ran your Macro but there are no results (my spreadsheet is empty).

gcotterl formulated on Sunday :
On Jan 30, 1:34*pm, GS wrote:
Here's what works for me. It takes a while to go through each cell in
colA, but takes less than a minute to process all 18513 entries.

Sub FindMatches()
* Dim wksSource As Worksheet, wksTarget As Worksheet
* Dim vVal As Variant, rng As Range
* Dim lSect1Cols As Long, lSect2Cols As Long
* Dim lSect1Rows As Long, lSect2Rows As Long
* Dim lNextRow As Long, i As Long

* 'Hide screen activity
* Application.ScreenUpdating = False

* Set wksSource = ActiveWorkbook.ActiveSheet
* Set wksTarget = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
* wksTarget.Name = wksSource.Name & "_2"

* With wksSource
* * lSect1Cols = .Range("$A$1:$N$1").Columns.Count
* * lSect2Cols = .Range("$O$1:$AC$1").Columns.Count
* * lSect1Rows = .Range("$A$1").End(xlDown).Row
' * *lSect2Rows = .Range("$O$1").End(xlDown).Row

* * For i = 1 To lSect1Rows
* * * vVal = .Cells(i, 1).Value
* * * Set rng = .Range("$O:$O").Find(what:=vVal)
* * * If Not rng Is Nothing Then '//we have a match
* * * * Application.StatusBar = "Found match for " & vVal
* * * * lNextRow = lNextRow + 1
* * * * .Cells(i, 1).Resize(1, lSect1Cols).Copy _
* * * * * * Destination:=wksTarget.Cells(lNextRow, 1)
* * * * rng.Resize(1, lSect2Cols).Copy _
* * * * * * Destination:=wksTarget.Cells(lNextRow, lSect1Cols + 1)
* * * End If
* * Next
* End With
* With wksTarget
* * .UsedRange.EntireColumn.AutoFit: .Activate
* End With
* Application.StatusBar = "" '//reset
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I ran your Macro but there are no results (my spreadsheet is empty).

Was the sheet with the lists the active sheet? When I ran it in your
wkb, it generated Sheets("MULTI_2") with 1483 rows of data.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 30, 4:05*pm, GS wrote:
gcotterl formulated on Sunday :





On Jan 30, 1:34 pm, GS wrote:
Here's what works for me. It takes a while to go through each cell in
colA, but takes less than a minute to process all 18513 entries.

Sub FindMatches()
Dim wksSource As Worksheet, wksTarget As Worksheet
Dim vVal As Variant, rng As Range
Dim lSect1Cols As Long, lSect2Cols As Long
Dim lSect1Rows As Long, lSect2Rows As Long
Dim lNextRow As Long, i As Long

'Hide screen activity
Application.ScreenUpdating = False

Set wksSource = ActiveWorkbook.ActiveSheet
Set wksTarget = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
wksTarget.Name = wksSource.Name & "_2"

With wksSource
lSect1Cols = .Range("$A$1:$N$1").Columns.Count
lSect2Cols = .Range("$O$1:$AC$1").Columns.Count
lSect1Rows = .Range("$A$1").End(xlDown).Row
' lSect2Rows = .Range("$O$1").End(xlDown).Row

For i = 1 To lSect1Rows
vVal = .Cells(i, 1).Value
Set rng = .Range("$O:$O").Find(what:=vVal)
If Not rng Is Nothing Then '//we have a match
Application.StatusBar = "Found match for " & vVal
lNextRow = lNextRow + 1
.Cells(i, 1).Resize(1, lSect1Cols).Copy _
Destination:=wksTarget.Cells(lNextRow, 1)
rng.Resize(1, lSect2Cols).Copy _
Destination:=wksTarget.Cells(lNextRow, lSect1Cols + 1)
End If
Next
End With
With wksTarget
.UsedRange.EntireColumn.AutoFit: .Activate
End With
Application.StatusBar = "" '//reset
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I ran your Macro but there are no results (my spreadsheet is empty).

Was the sheet with the lists the active sheet? When I ran it in your
wkb, it generated Sheets("MULTI_2") with 1483 rows of data.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Yes, the sheet with the lists was the active sheet.

I must be doing something wrong.
(I wasted all afternoon on this; it's most frustrating!!!!)

Can I re-send my spreadsheet to you so you can run your macro on it
and send the results back to me?

Yes, resend!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
I just awoke to the fact the same request is posted on public.excel.misc
by "Gary" under the title "Eliminate non-matching cells.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls - in the free folder)




"GS"
wrote in message
...
Yes, resend!
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jan 30, 5:45*pm, "Jim Cone" wrote:
Garry,
I just awoke to the fact the same request is posted on public.excel.misc
by "Gary" under the title "Eliminate non-matching cells.
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls - in the free folder)

"GS"
wrote in ...



Yes, resend!
--
Garry
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Since the macro didn't work, I thought I'd try to see it someone had
an other way.

Jim Cone wrote :
Garry,
I just awoke to the fact the same request is posted on public.excel.misc
by "Gary" under the title "Eliminate non-matching cells.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls - in the free folder)

Jim,
You just awoke me to the fact that I was not subscribed to that group!
I am now!<g

Yes, the OP's name is Gary. Unfortunately, he has difficulty explaining
what he wants in terms we understand (ie: Excel lingo), and so it takes
an awful lot of back&forth (as you can see) to get to where we can help
him. He seems to be working at getting better at how to express this. I
think it depends, really, on if he can decide what details we need to
know in order to best help him.

In time.., all in good time!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Gary,
The code was designed to work on the original file you sent me. I'll
return it to you with the results of running the code on
Sheets("MULTI").

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Since the macro didn't work, I thought I'd try to see it someone had
an other way.

The macro didn't work because you ran it on a sheet with different
column layouts than the sample sheet you sent me!

I have revised the macro to be more easily adaptable to different
column layouts. All that's required is to revise the section addresses
to match whatever sheet you want to run the macro on.

Revised code:

Sub FindMatches()
' Finds matching values in 2 sections of data on the same wks.
' Loops Section1,Column1 searching for matches in Section2,Column1.
' Puts found matches in same row on new sheet.

'Section addresses. (change/add as desired)
Const sRngSection1 As String = "$A$1:$N$1"
Const sRngSection2 As String = "$O$1:$AC$1"

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim vVal As Variant, vCalcMode As Variant, rng As Range
Dim lSection1_NumCols As Long, lSection2_NumCols As Long
Dim lSection1_NumRows As Long, lSection2_NumRows As Long
Dim lNextRow As Long, i As Long

Set wksSource = ActiveWorkbook.ActiveSheet
Set wksTarget = ActiveWorkbook.Sheets.Add(After:=ActiveSheet)
wksTarget.Name = wksSource.Name & "_2"

With Application
.ScreenUpdating = False
vCalcMode = .Calculation
.Calculation = xlCalculationManual
End With 'Application

With wksSource
lSection1_NumCols = .Range(sRngSection1).Columns.Count
lSection2_NumCols = .Range(sRngSection2).Columns.Count
lSection1_NumRows = .Range(sRngSection1).Cells(1,
1).End(xlDown).Row
' lSection2_NumRows =
..Range(sRngSection1).Cells(1,1).End(xlDown).Row

For i = 1 To lSection1_NumRows
vVal = .Cells(i, 1).Value
If vVal < "" Then
Set rng = .Range("$O:$O").Find(what:=vVal)
If Not rng Is Nothing Then '//we have a match
lNextRow = lNextRow + 1
Application.StatusBar = "Processing match #" & lNextRow
.Cells(i, 1).Resize(1, lSection1_NumCols).Copy _
Destination:=wksTarget.Cells(lNextRow, 1)
rng.Resize(1, lSection2_NumCols).Copy _
Destination:=wksTarget.Cells(lNextRow, lSection1_NumCols
+ 1)
End If 'Not rng Is Nothing
End If 'vVal < ""
Next
End With 'wksSource

'Cleanup...
With wksTarget
.UsedRange.EntireColumn.AutoFit: .Activate
End With 'wksTarget

With Application
.ScreenUpdating = True: .Calculation = vCalcMode: .StatusBar = ""
End With 'Application
End Sub

BTW, this works on BOTH samples you sent me.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

By the way, I'm using Microsoft Office Excel 2007.

I know that. What's your point?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS formulated the question :
Set rng = .Range("$O:$O").Find(what:=vVal)

Change the above line (in the loop) to:

Set rng = .Range(sRngSection2).EntireColumn.Find(what:=vVal)

...so it doesn't need to be revised to suit.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS explained :
GS formulated the question :
Set rng = .Range("$O:$O").Find(what:=vVal)

Change the above line (in the loop) to:

Set rng = .Range(sRngSection2).EntireColumn.Find(what:=vVal)

..so it doesn't need to be revised to suit.

Actually, that's way too slow so revise further as follows...

Set rng = .Range(sRngSection2).Cells(1).EntireColumn.Find(wh at:=vVal)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc