VBA with conditions

[AdiVohra]

Hi,

I have a table as such which was produced by recording a macro. I originally had a large amount of data and filtered out the column called "Der" to only produce anything greater than 5. This produces below.

Cur	Spo	Var	Clar	Der	Rar	Loh
USD-SGD	0.659	2.3	2.36	7.23	2	12.31
USD-THB	0.546	6.12	1.23	12.321	31.3	1.326
SGD	0.95	3.26	1.023	7.23	1.321	1.0322
GBP	1.6023	14.2	3.32	6.522	1.636	3.322
THB	12.56	3.21	40.32	12.325	1.3	3.2

<colgroup><col span="3"><col><col><col><col></colgroup><tbody>
</tbody>

After I stopped recording this macro, I now need to add to the VBA so that the output is just the "Der" and "Cur" columns, ignoring the rest. Also, as can be seen, the first 2 rows are given as a currency pair and the last 3 rows are the currency on its own. I only need to output the rows that are presented as a pair (e.g. USD-SGD and not rows with a single currency e.g. SGD) bearing in mind the amount of "paired outputs" may vary from time to time so needs to be open ended to add more?

Hope someone can help

 

[mrmmickle1]

If your Data Looks Like This:

Excel 2012
	A	B	C	D	E	F	G
1	Cur	Spo	Var	Clar	Der	Rar	Loh
2	USD-SGD	0.659	2.3	2.36	7.23	2	12.31
3	USD-THB	0.546	6.12	1.23	12.321	31.3	1.326
4	SGD	0.95	3.26	1.023	7.23	1.321	1.0322
5	GBP	1.6023	14.2	3.32	6.522	1.636	3.322
6	THB	12.56	3.21	40.32	12.325	1.3	3.2
7	USD-FGH	12.56	3.21	40.32	3	1.3	3.2
8	FGH-GHJ	12.56	3.21	40.32	2	1.3	3.2
9	HJK	12.56	3.21	40.32	1	1.3	3.2
10	GHJ	12.56	3.21	40.32	1	1.3	3.2
Sheet1

Then use this Code:

[COLOR=#0000ff]Sub [/COLOR]FilterData()

    [COLOR=#0000ff]Dim [/COLOR]LR [COLOR=#0000ff]As Long[/COLOR]

    LR = Range("A" & Rows.Count).End(xlUp).Row
        ActiveSheet.Range("$A$1:$G$" & LR).AutoFilter Field:=5, Criteria1:=">5", Operator:=xlAnd
        ActiveSheet.Range("$A$1:$G$" & LR).AutoFilter Field:=1, Criteria1:="*-*", Operator:=xlAnd

[COLOR=#0000ff]End Sub[/COLOR]