I want to extract the rows when a column met criteria

**wantaku11** · 11-11-2012, 11:50 PM

Hi Everyone,

I need assistance on how to extract rows from sheet1 to sheet2 when a criteria met in a specific column. Example: if column B value >=(more than) 75% then the row that having the value will be copied and paste to another worksheet.

**simeon.hruby** · 11-12-2012, 03:59 AM

wantaku11,

Here is a macro that will do what you want. All you need to change is the Sheet Names that you are copying and pasting to.

Please Login or Register  to view this content.

In line 4 "If Cells(r, 2) >= 0.75 Then" this is stating that any value in column B "Cells(r,2)" with the number two meaning B because it is the second column that are >= to 0.75 or 75% will be cut and pasted to "Sheet2".

Please Login or Register  to view this content.

So all of the areas that contain either "Sheet1" or "Sheet2" need to be replaced by the sheet you want to copy from to the sheet you wish to paste in.

The sections below the first "Next r" will look for blanks in column B on both sheets and delete those rows.

I hope this helps!

Simeon

**wantaku11** · 11-12-2012, 05:22 AM

Hi Simeon,

Thanks ... It works.

I've been cracking my head how to do it ...

But Thanks to you I manage to cool my brain ...

Can I ask you one more question to complete the whole package,

If i want to fill the row (from column A to column B only) how can i do it?

Let say between 75% and 89% is orange
between 90% and 99% is blue
100% and above is red.

The fill only happen before the transfer of the row to another sheets.

hope you can helpe me out.

Cheers

**simeon.hruby** · 11-12-2012, 09:20 AM

wantaku11,

Sure. You could use.

Please Login or Register  to view this content.

I hope this is what you were asking for!

Simeon

**wantaku11** · 11-16-2012, 05:32 AM

Hi Simeon,

thank you again. I manage to edit the code accordingly.

Just want to ask you the meaning of this code;

For r = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1

Previosly I always used For r = 1 to 200

What is the difference between both code ?

because i hope in the future my macro language will be clever than current one.

**simeon.hruby** · 11-16-2012, 05:56 AM

I use that line so that the macro starts at the bottom of the data and then works it's way up. This way it doesn't matter how many rows of data you have because it is not in any fixed selection.

Simeon

**wantaku11** · 11-19-2012, 03:29 AM

Simeon,

thank you so much ... i managed to edit the macro according to my original specs.

and i kind of understand how your counting works...maybe i should use that style more often ... at least the code do the counting according to available rows...

With this i think i consider everything is solved ... i'm glad to have you to assist me ...

Cheers
Simeon.

p/s:- in future if i need some help ... is it ok for me to contact u instead in the forum ? ^_^