PDA

View Full Version : [SOLVED] After filtering row16 down, fill row10 with top visible row. If no results do nothing



frank_m
04-17-2011, 04:18 AM
I'm filtering a sheet from row 16 to the last used row. - What I need to do is fill row 10 with the top visible row of data in the results, but if there are no results, leave row 10 untouched.

Thank you much for you time.

frank_m
04-17-2011, 06:17 AM
Hello All,

I managed to put the code below together by looking at a few other routines I'm using for other tasks. Some I got here, some at the Ozgrid site via google.

If anyone has a cleaner way of doing it, or if you see any issues I'd love to hear about it.

Thanks

Dim rCell As Range
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(2).SpecialCells(xlCellTypeVisible).Count - 1 = rng.Columns(2).Rows.Count - 1 Then
MsgBox ("Rows are not Filtered")
Exit Sub
End If
Set rng = Range("B16:B" & Cells(Cells.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then
For Each rCell In rng
If rCell.Row > 15 Then
Range("A10:Z10").Value = rCell.EntireRow.Cells(1).Resize(, 27).Value
Exit For
Else
MsgBox "No results found"
End If
Next rCell
End If