VBA Advanced Filter

  • Thread starter Thread starter Robert
  • Start date Start date
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
 
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)
 
Back
Top