VBA Advanced Filter

R

Robert

Hello,

Can some one tell me which VBA statement to use for the following:
A B C D E F
Name Mkt cap Weight 20days 3months 6months

CHEUNGLTD 15961 2.36% 0.51 0.47 0.49
TIANJIN CAP 523 0.33% 7.50 5.56 5.89
HANG LTD 19716 1.49% 0.61 0.66 0.69
SINOPEC 2220 0.92% 2.25 3.03 2.64
VALUE PART 51 1.29% 492.10 714.38 619.33
MIDLAND LTD 299 0.85% 4.51 3.21 2.14
HUTCHISON LTD 28546 1.79% 0.23 0.18 0.20
FIRST CO 770 2.57% 13.31 11.15 10.24
SUN HUNG 16604 1.85% 0.37 0.37 0.38
SWIRE LTD 9232 1.01% 0.66 0.59 0.55

Now I would like to split the datarange based on column B (Mkt Cap).
For example, give me all the names (including weights and periods,
column C:F) that have a Mkt cap <500. I would like to do the same for
Mkt cap >500 <1000, and >1000.
As I need to customize the filtered data afterwards it is desirable to
paste the results next to each other.

Thanx very much for helping me out!

Regards,
Robert
 
B

Bob Phillips

This will work for a value between 0 and less than 500

Dim rng As Range

Application.ScreenUpdating = False
Range("A1").EntireRow.Insert
Range("B1").Value = "Temp"
Set rng = Range("B1").Resize(Cells(Rows.Count, "B").End(xlUp).Row)
rng.AutoFilter Field:=1, _
Criteria1:=">=0", _
Operator:=xlAnd, _
Criteria2:="<500"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
Worksheets("Sheet3").Range("A1")
Worksheets("Sheet3").Range("A1").EntireRow.Delete
rng.AutoFilter
Range("B1").EntireRow.Delete
Application.ScreenUpdating = True


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top