Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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 -- Boris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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??? BorisS wrote: 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 -- Boris -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
I think that there is a difference between pasting a copied range and entering a
formula like you did. BorisS wrote: 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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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? -- Boris "Dave Peterson" wrote: I think that there is a difference between pasting a copied range and entering a formula like you did. BorisS wrote: 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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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. BorisS wrote: 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? -- Boris "Dave Peterson" wrote: I think that there is a difference between pasting a copied range and entering a formula like you did. BorisS wrote: 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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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 "Dave Peterson" wrote: 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. BorisS wrote: 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? -- Boris "Dave Peterson" wrote: I think that there is a difference between pasting a copied range and entering a formula like you did. BorisS wrote: 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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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 BorisS wrote: 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 "Dave Peterson" wrote: 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. BorisS wrote: 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? -- Boris "Dave Peterson" wrote: I think that there is a difference between pasting a copied range and entering a formula like you did. BorisS wrote: 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. -- Boris "Dave Peterson" wrote: 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??? BorisS wrote: 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 -- Boris -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtered pasting
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 Dave Peterson wrote: This doesn't have too many validity checks, but it works if you select nice ranges. <<snipped |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying filtered data to another Excel Spreadsheet | Excel Discussion (Misc queries) | |||
Pasting Onto Filtered Data | Excel Discussion (Misc queries) | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
Pasting onto filtered cells | Excel Worksheet Functions | |||
Pasting onto filtered cells | Excel Worksheet Functions |