Copying Values in Filtered Ranges

[EliseFreedman]

I am working on a fairly large spreadsheet.

I would like to filter a range and then copy the value in the first row of one of the columns of this range into the remaining rows of that column.

I managed to create the following macro
<code>
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Y"
Range("Z45").Select
ActiveCell.FormulaR1C1 = "1"
Range("Z45").Select
Selection.Copy
Range("Z108:Z542").Select
ActiveSheet.Paste
Range("AA45:AA542").Select
Selection.Interior.ColorIndex = 37
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 45
Selection.Font.ColorIndex = 37
Range("AA45").Select
Selection.AutoFilter Field:=1
</code>

It may not be the same range of cells each time so how can i change the code so that it automatically goes to the first cell in a column in the range and then copies the value to the remaining rows.

Also how do i use code to select the entire filtered range where the cells included within the range might change.

 

[Loomah]

Hi
This is a really convoluted way (I'm sure there's an easier way) to find the second visible cell in column B of a filtered List. I'm assuming headings are in row one otherwise it would be a case of selecting cell B1!!

You should then have a look at the FillDown method in help.

You'll need to consider whether the value you fill in should only go into the filtered cells or all cells.

With [a1].CurrentRegion.Columns("B").SpecialCells(xlCellTypeVisible)
    If .Areas(1).Cells.Count > 1 Then
        .Areas(1).Cells(2, 1).Select
    Else
        .Areas(2).Cells(1, 1).Select
    End If
End With

As for your second question - the answer is in my reply to thefirst!

[a1].CurrentRegion.SpecialCells(xlCellTypeVisible)

I feel sure you're going to need to come back with further questions on this!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​