filtered pasting

[Guest]

I'm trying to paste a row of formulas down a list, but in filtered mode.
Problem is that it's pasting into the lines that are not visible in the
filter (those that are hidden). I thought that at one point I was pasting
things and it respected the visibility factor of the filter (in other words,
only pasting into those lines that were seen).

I turned calculation to manual for ease of use (large list, pivots, etc),
but I cannot imagine that has anything to do with it. Is there some other
setting, or some particular way I have to do this (obviously aside from
ctrl-selecting each line individually, which is not an option with the number
of lines I have.

Thx for any help

 

[Dave Peterson]

Excel doesn't have anything that lets you do this built into it.

But maybe you could sort your data, then paste into that contiguous range.

Then resort and reapply the filter???

 

[Guest]

Are you sure there are no functions that can "mass apply" to only the
filtered portion of a list? I could have sworn I've done that before.

 

[Guest]

I just tried it with a simple set of rows outside of that sheet. Basically
did an OR on the filter to where I specifically ended up with the two rows
which were on the top and bottom of the range of rows. Applied a formula to
the shown cells (highlighting straight through, so in theory "including" the
hidden rows). When I took off the filter, the rows that were hiddend did not
have that formula applied. So at least I proved to myself that I wasn't, in
this case, crazy for thinking I'd done it before.

Now the question is why a particular sheet would NOT apply that logic, and
would write formulas into the hidden rows that were filtered out.

 

[Dave Peterson]

I think that there is a difference between pasting a copied range and entering a
formula like you did.

I just tried it with a simple set of rows outside of that sheet. Basically
did an OR on the filter to where I specifically ended up with the two rows
which were on the top and bottom of the range of rows. Applied a formula to
the shown cells (highlighting straight through, so in theory "including" the
hidden rows). When I took off the filter, the rows that were hiddend did not
have that formula applied. So at least I proved to myself that I wasn't, in
this case, crazy for thinking I'd done it before.

Now the question is why a particular sheet would NOT apply that logic, and
would write formulas into the hidden rows that were filtered out.

 

[Guest]

I was hoping you were right, and then I copied just the one cell with the
right formula, highlighted just the set of filtered cells under one column,
and it still didn't do it (in other words, overwrote hidden rows).

Any other thoughts, or something on your end you're doing that I may be
missing?

 

[Dave Peterson]

Just my original suggestions to sort, copy|paste, and re-sort.

You could write a macro that cycles through each of the cell in the copied range
and pastes into the visible cells.

I was hoping you were right, and then I copied just the one cell with the
right formula, highlighted just the set of filtered cells under one column,
and it still didn't do it (in other words, overwrote hidden rows).

Any other thoughts, or something on your end you're doing that I may be
missing?

 

[Guest]

Any chance you would have some code that works for that macro? I only know
the only that copy the things I'm doing, and I always have a tough time with
the ones that I have to use properties like visible and things like that.

 

[Dave Peterson]

This doesn't have too many validity checks, but it works if you select nice
ranges.

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim RngToPaste As Range
Dim myCell As Range
Dim iRow As Long

Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox _
(Prompt:="Select a single column range to copy", Type:=8) _
.Areas(1).Columns(1)
On Error Resume Next

If RngToCopy Is Nothing Then
Exit Sub
End If

Set RngToPaste = Nothing
On Error Resume Next
Set RngToPaste = Application.InputBox _
(Prompt:="Select a single column range to paste", _
Type:=8).Areas(1).Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error Resume Next

If RngToCopy.Cells.Count > RngToPaste.Cells.Count Then
MsgBox "not enough visible cells"
Exit Sub
End If

iRow = 0
For Each myCell In RngToPaste.Cells
RngToCopy.Cells(1).Offset(iRow, 0).Copy _
Destination:=myCell
iRow = iRow + 1
If iRow > RngToPaste.Cells.Count Then
Exit For
End If
Next myCell

End Sub

Any chance you would have some code that works for that macro? I only know
the only that copy the things I'm doing, and I always have a tough time with
the ones that I have to use properties like visible and things like that.
--
Boris

Just my original suggestions to sort, copy|paste, and re-sort.

You could write a macro that cycles through each of the cell in the copied range
and pastes into the visible cells.

 

[Dave Peterson]

I meant to add one more check...

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim RngToPaste As Range
Dim myCell As Range
Dim iRow As Long

Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox _
(Prompt:="Select a single column range to copy", Type:=8) _
.Areas(1).Columns(1)
On Error Resume Next

If RngToCopy Is Nothing Then
Exit Sub
End If

Set RngToPaste = Nothing
On Error Resume Next
Set RngToPaste = Application.InputBox _
(Prompt:="Select a single column range to paste", _
Type:=8).Areas(1).Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error Resume Next

'added this
If RngToPaste Is Nothing Then
Exit Sub
End If

If RngToCopy.Cells.Count > RngToPaste.Cells.Count Then
MsgBox "not enough visible cells"
Exit Sub
End If

iRow = 0
For Each myCell In RngToPaste.Cells
RngToCopy.Cells(1).Offset(iRow, 0).Copy _
Destination:=myCell
iRow = iRow + 1
If iRow > RngToPaste.Cells.Count Then
Exit For
End If
Next myCell

End Sub

This doesn't have too many validity checks, but it works if you select nice
ranges.

<<snipped>>