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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,600
Messages
5,832,650
Members
430,151
Latest member
Kacbear

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top