# Set range on filtered lines and specific columns of a table

#### vissers9

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
ABCDE
1hdg1hdg2hdg3hdg4hdg5
2a28861764
3s83205730
4d2263892
5s36844552
6a74604680
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

Code:
``````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``````

Hi Venkat1926,

thanks for your solution, it does the trick.

Rob

Replies
6
Views
151
Replies
7
Views
247
Replies
1
Views
170
Replies
3
Views
223
Replies
6
Views
330

1,203,506
Messages
6,055,806
Members
444,825
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back