Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible not to specify a cell reference when data is filter
Define a range of the filtered cells, something like this:
Dim entirerng As Range, filtrng As Range, filtcell As Range '... Columns("A").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="TRUE" Set entirerng = ActiveSheet.AutoFilter.Range.Columns(1) Set entirerng = entirerng.Offset(1, 0).Resize(entirerng.Rows.Count - 1) On Error Resume Next Set filtrng = entirerng.SpecialCells(xlVisible) On Error GoTo 0 For Each filtcell In filtrng ' your code here! refer to the filtered cell as filtcell, e.g. filtcell.Value = "newvalue" Next filtcell (Courtesy of Bob Philips!) Regards, Stefi €žSarah (OGI)€ť ezt Ă*rta: I'm trying to create a macro that will filter a specific column on one of my worksheets and will perform a calculation on each row of the resulting list of cells. However, the code that's created references cell AA71 (the first cell listed when the code was created) but this might change everytime the source data is updated, i.e. I've repeated the process now and the fist cell is AA2. Is there any way of not specifying a particular cell reference? (The first row required will always be the first row under the row header.) Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference cell from advanced filter | Excel Worksheet Functions | |||
Apply filter and cell reference | Excel Programming | |||
Pivot Table - filter by a cell reference | Excel Programming | |||
Filter Macro with Cell reference | Excel Programming | |||
Filter and show data based on a reference cell | Excel Worksheet Functions |