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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
816
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
579
epactheactor
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
488
John_w
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,539
Messages
6,120,100
Members
448,944
Latest member
SarahSomethingExcel100

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