• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can hidden destination rows be included when pasting?

U

UniMord

New Member
Hi All!

I'd like to copy a value into a filtered range and include the hidden cells. When pasting a series of cells, this is the default, usually undesired, behavior, i.e., your values disappear into a filter sinkhole.
But, when the value originates from a single cell, is a <Ctrl><Enter>, or even a drag of the pull handle, hidden cells are ignored. Picture a filtered table, where we want to enter FALSE into all the visible cells and everything in between (i.e., hidden). Is there any easy way to achieve this without having to clear the filters?

As a last resort, this can be solved by looping through the range, like below. Is there a native way to accomplish this?

Thank you,

UniMord

Code: 
Sub PasteIncludingHidden()
  On Error GoTo Finish 'If clipboard is empty.
  Dim cel As Range
  Dim DataObj As New MSForms.DataObject: DataObj.GetFromClipboard
  Dim paste$: paste = DataObj.GetText: paste = Left(paste, Len(paste) - 2)
  Application.ScreenUpdating = False
  For Each cel In Selection
  cel = paste
  Next cel
Finish:
  Application.ScreenUpdating = True
End Sub
 
Thank you SirJB7. What you've advised, unfortunately for me, is the precise opposite of what I'm seeking to accomplish. In the scenario presented in the original question, we're faced with a filtered table, where I want to paste in a value, while including the hidden cells as well. Imagine a table where, due to a number of filters applied, Row 200 appears immediately after Row 50. I'd like to paste FALSE into the entire range Q50:Q200, i.e., the 2 visible cells (Q50 and Q200) are endpoints. This can achieved via a loop, but I'm hoping there exists a native solution. Thank you again.
 
Hi, UniMord!

It seems as if I read to quickly and led you to the common place. Now let me point you to a not so wrong place, at least.

To copy all rows from a subset of a filtered range (e.g., filtered odd rows, selected rows 13 to 29, 9 visible rows), that's 17 rows, put this code somewhere, assign that Sub to a macro shortcut, and it should work.

Code: 
Option Explicit

Sub CopyBlind()
    ' constants
    ' declarations
    Dim lFrom As Long, lTo As Long
    ' start
    With Selection
        lFrom = .Rows(1).Row
        lTo = .Rows(.Rows.Count).Row
    End With
    ' process
    Range(Rows(lFrom), Rows(lTo)).copy <destination>
End Sub

Remember setting <destination> to the proper output place. It avoids the loop you mentioned and it performs the copy in a single pass, useful if you have a lot of rows involved.

Regards!
 
Thank you. I just tried plugging in your code. It doesn't paste into the filtered rows, it only pastes to the visible cells. I'd be very interested in obviating the loop, but it still seems to be the only proven way for me to get the values into the hidden cells.
 
Last edited:
Hi, UniMord!

The code works for a selected range of rows (from, to) in a wider range either filtered or not. The only condition is that you have to manually select the filtered rows (in your case) that are to be copied, and change the destination that's written as a dummy value <destination>.

If you can't manage to handle this, consider uploading a sample file.

Regards!
 
Try:
Code: 
Sub PasteIncludingHidden()
Dim DataObj As New MSForms.DataObject: DataObj.GetFromClipboard
Dim paste$: paste = DataObj.GetText: paste = Left(paste, Len(paste) - 2)
Selection.PasteSpecial paste:=xlPasteAll, SkipBlanks:=True
'or:
'Selection.PasteSpecial paste:=xlPasteValues
End Sub
 
Last edited:
If you had read the rules you would have seen two bits that apply to your post,

Cross posting

1/ Important to you, but not for rest of the members here. These words will be moderated out.
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
  • Please don't attach or post links to copyright material including Books, Videos etc. Your post will be deleted

You have crossed posted here:-

https://www.mrexcel.com/forum/excel-questions/1006161-how-do-i-paste-single-value-filtered-range-include-hidden-cells.html

2/ Posting in the right section of the forum various reasons for this
Your post and any help given will not show in the VBA section to anyone looking through post who has a similar problem
3/ Like wise it will not show in a VBA search as it is in the general question section. As the rules point out.

progress and of course if you are answering questions you may need to ask questions of the initial poster.
  • Please post, new posts in the correct forums, not as Emails/Messages to people
  • Quickly search the Web and Chandoo.org for help before posting. It is quite possible that your problem has been solved by someone else.

Your post was reported by another member who probably does not understand why new members cannot be bothered to read a few simple rules that help keep this forum at the top.


.
 
p45cal
Thank you for your solution. Since there doesn't appear to be any non-VBA solution available, at least I can avail myself of your far more efficient code than what I'd originally suggested.

SirJB7
Thank you for your efforts. Please see p45cal's answer. It seems to me that your code doesn't quite address the question. I'm not selecting a filtered range to copy from, I'm pasting a single value (e.g., TRUE) into a filtered range and I'd like the value to populate the entire range, including the hidden cells. Please have a look at the sample file. I've included my original, inefficient code, and p45cal's improved code.

Bob
Ouch! I'm not quite as evil as all that, and I did, in fact, read the rules. I understand your ire at people littering the boards with non-conforming questions. I certainly didn't intend to violate the letter or the spirit of the forum's rules. Regarding the 3 points that you raised, please allow me to clarify:

1) Cross-Posting: I don't cross-post without reasonable cause. My original question at MrExcel.com had gone unanswered for nearly a week before I posted here. If it wasn't answered by then, it wasn't likely to be answered at all (note: still no responses there) - I wasn't seeking to have my question answered "faster", it was a matter of getting it answered at all. Additionally, my question on this forum was clearer and better formulated than my earlier question, hence, I felt it wouldn't benefit anyone to reference the earlier version. Nonetheless, if such a situation arises in the future, I will note that an outdated question was asked elsewhere, if that is the requirement.

2 & 3) Correct Forum: My question was very specific, that I knew how to solve this in VBA (I even posted the code), and I was asking if there existed a native (i.e., non-VBA) way to achieve the same end. Since I wasn't asking for VBA help at all, the question would not fall under the VBA category.

Peace.
 

Attachments

Last edited:
UniMord said:
Is there a native way to accomplish this?
Click to expand...
While I don't know of a way to fill the hidden cells of a filtered range with a value typed directly in and Ctrl+Enter being pressed, you may have guessed from my supplied snippet of code that you can copy into such hidden cells by a manual Paste|Special, ticking the Skip Blanks box or Pasting Values.

Just curious; why do you lose the last two characters of the clipboard contents?
 
Thank you! That is exactly what I was after. An Alt+HVV gets the job done in a blink of an eye, no macro needed.

For some reason, GetText was tacking on a CRLF, so I was slicing it off.
 
You must log in or register to reply here.
Back
Top