Identify the Text & Copy the Row

P

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
125
Kaper
Kaper
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,224,514
Messages
6,179,223
Members
452,896
Latest member
IGT

Share this page

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
Back
Top