range loop? help

[fishy]

I have a series of queries that uses a range to autofilter.

This works by working through a list in cells C8 to C34.

At present I use the code detailed below but know there must be a way of
excel working through this list more intelligently i.e. not having to name
the cell to copy paste but work through the range in order.

-----------------------------------------------------------------------------------------
Sub EditPt2()

'Filter each district

'District 1
Sheets("Control").Select
Range("C8").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

'District 2
Sheets("Control").Select
Range("C9").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call FilterDistricts

etc etc etc.

 

[Jacob Skaria]

Not sure what the procedure does; but try the below loop...


Dim lngRow As Long

Sheets("Control").Select
For lngRow = 8 To 34
Range("C6") = Range("C" & lngRow).Value
Call FilterDistricts
Next

 

[Javed]

I have a series of queries that uses a range to autofilter.

This works by working through a list in cells C8 to C34.

At present I use the code detailed below but know there must be a way of
excel working through this list more intelligently i.e. not having to name
the cell to copy paste but work through the range in order.

---------------------------------------------------------------------------­--------------
Sub EditPt2()

'Filter each district

'District 1
    Sheets("Control").Select
    Range("C8").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False

    Call FilterDistricts

'District 2
    Sheets("Control").Select
    Range("C9").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False

    Call FilterDistricts

etc etc etc.

Follow the following codes:
******************************
Dim Rng as Range,

For each Rng in Worksheets("Control").range("c8:c34")
Rng.copy
Worksheets("Control").Range("c6").pastespecial
paste:=xlpastevalues
Call FilterDistricts
Next Rng
********************************