Set range on filtered lines and specific columns of a table

[vissers9]

Hi all,

I have a table which is refreshed from a view on our SQL server. I use a part of this data further on and would like to automate the following:
I would like to have a range set which I can use to loop through data.
The range should hold the filtered data of table "PremiumTable", the columns "Grouping2", "Price", "Grower"

I have found a couple of posts giving the ability to create a range of visible cells and specific columns, but I wasn't able to combine this yet.

Could you help me with this?

Thanks,

Rob

 

[venkat1926]

I hope you know how to write a macro to do autofilter . I assume autofilter has been done and filtered data is like this

Sheet1

Excel Workbook
	A	B	C	D	E
1	hdg1	hdg2	hdg3	hdg4	hdg5
2	a	28	86	17	64
3	s	83	20	57	30
4	d	22	6	38	92
5	s	36	84	45	52
6	a	74	60	46	80
Sheet1

you do; manually filter for column A and column D (later you can do it by a macro)

now you wana to filtered cil A and D to sheet 2
use this macro



after the data is filtered try this macro (assume you want filtered data in co;l A aND D)
modify macro suitably

Sub test()
Dim r As Range, filt(1 To 2) As Range
Dim j As Integer
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion


Set filt(1) = r.Columns("A:A").SpecialCells(xlCellTypeVisible)
Set filt(2) = r.Columns("d:d").SpecialCells(xlCellTypeVisible)
For j = 1 To 2
filt(j).Copy
Worksheets("sheet2").Range("A1").Offset(0, j - 1).PasteSpecial
Next j
With Worksheets("sheet1")
.AutoFilterMode = False
.Application.CutCopyMode = False
End With
End Sub

 

[vissers9]

Hi Venkat1926,

thanks for your solution, it does the trick.

Rob