Identify the Text & Copy the Row

Pratap 1987

Board Regular
Joined
Feb 28, 2011
Messages
95
Hi,
I am looking for a Visual Basic Coding which identifies a given text (in a particular column) and copies the entire row (of the identified text) to a new sheet.

Below is a dummy data for your reference.

<TABLE style="WIDTH: 398pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=530><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" span=2 width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=73>G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=73>H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>2000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Difference</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Difference</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>345</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>31000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>345</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>2000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Difference</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Difference</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>654</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>3000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>654</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>3000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>789</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>4000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>789</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>4000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>543</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>543</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>468</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>468</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>852</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1000000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>879</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>11000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>421</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>98000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>852</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1000000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Common</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>#N/A</TD></TR></TBODY></TABLE>
Assume that the above data are available in Sheet1
  • The coding should look for the text "Difference" in COLUMN- G & H and copy the entire ROWS to Sheet2 (Below should be the output in Sheet 2)
123 2000 123 1000 Difference Common
345 31000 345 2000 Common Difference
  • The coding should also look for "#N/A" in COLUMN- G and copy the entire ROWS to Sheet3 (Below should be the output in Sheet 3)
852 1000000 879 11000 #N/A Common
  • The coding should also look for "#N/A" in COLUMN- H and copy the entire ROWS to Sheet4 (Below should be the output in Sheet 4 )
    421 98000 852 1000000 Common #N/A
I tried using the filter option in Excel. But it is taking time to filter the results since the data is huge (around 30000 rows).

Hence, a coding to pull the above requirements would be good.
Any immediate assistance would be of great help.

Thanks,
Pratap 1987
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I'd probably approach this using a filtering approach, but in code, you have the advantage of preventing the screen from refreshing as you work, so it tends to be faster.

The methodology is to filter for the data you need, then copy and paste all visible rows (look at .specialcells(xlCellTypeVisible) for help with this) then remove the filter.

You could probably quickly find a lot of examples of this on the board which can be adapted to suit your needs.

HTH
 

Pratap 1987

Board Regular
Joined
Feb 28, 2011
Messages
95
Hi,

I tried doing it using Filtering option. But, it is taking time (more than 15 mins) since the worksheet has around 30000 rows in it.

Could you help me out with a Macro code?
I believe that would not take much of time.

Thanks,
Pratap.D
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I might be able to help - I have an idea how to do this using advanced filter, which is usually a bit quicker for this sort of thing. To help with this, I'll need to know what your column headers are - I'm assuming they're in row 1, please let me know if this is the case.

Also,

Are the #n/a's text or formula results?
Does formatting need to be copied also?
Am I right in thinking the word 'difference' could appear in either column and the data would still need to be shunted to sheet1? Your sample data has the word in both columns, but the example suggests otherwise.

HTH
 
Last edited:

Pratap 1987

Board Regular
Joined
Feb 28, 2011
Messages
95

ADVERTISEMENT

Hi,

Thanks for looking at this.

The column headers are A, B, C, D, E, F, G & H. Wherein the Column C & F has no data and remains vacant.

The #N/A are formula results.

Not necessary. Only the text and numbers would be fine. And formatting need not be copied.

Yes, you are correct. The word 'Difference' would appear in both the columns - G & H. Sorry about the mistake. Below is the updated table.
<TABLE dir=ltr border=0 cellSpacing=0 cellPadding=0 width=530><TBODY><TR><TD height=20 vAlign=center width="12%">A
</TD><TD height=20 vAlign=center width="12%">B
</TD><TD height=20 vAlign=center width="12%">C
</TD><TD height=20 vAlign=center width="12%">D
</TD><TD height=20 vAlign=center width="12%">E
</TD><TD height=20 vAlign=center width="12%">F
</TD><TD height=20 vAlign=center width="14%">G
</TD><TD height=20 vAlign=center width="14%">H
</TD></TR><TR><TD height=20 vAlign=center width="12%">321
</TD><TD height=20 vAlign=center width="12%">1000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">123
</TD><TD height=20 vAlign=center width="12%">1000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Difference
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">345
</TD><TD height=20 vAlign=center width="12%">31000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">345
</TD><TD height=20 vAlign=center width="12%">2000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Difference
</TD></TR><TR><TD height=20 vAlign=center width="12%">654
</TD><TD height=20 vAlign=center width="12%">3000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">654
</TD><TD height=20 vAlign=center width="12%">3000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">789
</TD><TD height=20 vAlign=center width="12%">4000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">789
</TD><TD height=20 vAlign=center width="12%">4000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">543
</TD><TD height=20 vAlign=center width="12%">5000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">543
</TD><TD height=20 vAlign=center width="12%">5000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">468
</TD><TD height=20 vAlign=center width="12%">10000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">468
</TD><TD height=20 vAlign=center width="12%">10000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">852
</TD><TD height=20 vAlign=center width="12%">1000000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">879
</TD><TD height=20 vAlign=center width="12%">11000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">#N/A
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">421
</TD><TD height=20 vAlign=center width="12%">98000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">852
</TD><TD height=20 vAlign=center width="12%">1000000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">#N/A
</TD></TR></TBODY></TABLE>
 
And the output in Sheet 2 should be : -
<TABLE dir=ltr border=0 cellSpacing=0 cellPadding=0 width=530><TBODY><TR><TD height=20 vAlign=center width="12%">321
</TD><TD height=20 vAlign=center width="12%">1000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">123
</TD><TD height=20 vAlign=center width="12%">1000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Difference
</TD><TD height=20 vAlign=center width="14%">Common
</TD></TR><TR><TD height=20 vAlign=center width="12%">345
</TD><TD height=20 vAlign=center width="12%">31000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="12%">345
</TD><TD height=20 vAlign=center width="12%">2000
</TD><TD height=20 vAlign=center width="12%">
</TD><TD height=20 vAlign=center width="14%">Common
</TD><TD height=20 vAlign=center width="14%">Difference
</TD></TR></TBODY></TABLE>

Thanks,
Pratap 1987
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Checked OK in XL2007

Code:
Sub pratap_1987()
    Dim dataRange, critSheet
    Dim crit(2), destRange
    Application.ScreenUpdating = False
    Set critSheet = Worksheets.Add
    With critSheet
        .Range("A1:B1").Value = Sheets("sheet1").Range("G1:H1").Value
        .Range("A2,B3") = "Difference"
        .Range("D1") = Sheets("sheet1").Range("G1")
        .Range("F1") = Sheets("sheet1").Range("H1")
        .Range("D2,F2") = "#N/A"
        Set crit(0) = .Range("A1:B3")
        Set crit(1) = .Range("D1:D2")
        Set crit(2) = .Range("F1:F2")
    End With
    With Sheets("sheet1")
        Set dataRange = .Range("A1:H" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    With dataRange
        For i = 0 To 2
            With Sheets("sheet" & i + 2)
                Set destRange = .Range("A1")
                .Cells.ClearContents
            End With
            .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=crit(i), CopyToRange:=destRange, Unique:=False
        Next i
    End With
    Application.DisplayAlerts = False
    critSheet.Delete
    Application.DisplayAlerts = True
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top