Results 1 to 5 of 5

VBA needed to Copy PasteSpecial Values Skip Blanks

This is a discussion on VBA needed to Copy PasteSpecial Values Skip Blanks within the Excel Questions forums, part of the Question Forums category; I'm trying to use VBA code to Copy/Paste Values/Skip Blanks based on the following: There are anywhere from 500-1200 rows ...

  1. #1
    Board Regular
    Join Date
    Jul 2012
    Posts
    61

    Default VBA needed to Copy PasteSpecial Values Skip Blanks

    I'm trying to use VBA code to Copy/Paste Values/Skip Blanks based on the following: There are anywhere from 500-1200 rows of data. Column O contains a value of either 0 or 1. I want the macro to start at the bottom of the list (row 1200) and using column O as the reference, work its way up the page...if the value is "0", it needs to select an offset range several columns to the right of column O ... then "copy" .. then move up a row and Paste Special...Values...Skip Blanks. I have code right now that works, however, it's not very efficient, taking from 5-6 minutes to process. I belive it's because I'm actually "selecting" the range each time. I am hoping to find a way to bypass the "selection" process. I've used this before on a simple copy/paste, i.e. Range("A1").Copy Range("A2") . I can't figure out how to adapt that syntax to incorporate Range Offset + Paste Special + Values + Skip Blanks. Below is the section of code that I'm using right now, which actually does "work":

    * Note: the header in column O is "1st Event Row", so the macro loops from the bottom up & stops when it gets to the header

    Do Until CStr(Selection.Value) = "1st Event Row"
    If ActiveCell.Value = 0 Then
    ActiveCell.Offset(0, 56).Select
    ActiveCell.Range("A1:CA1").Select
    Selection.Copy
    ActiveCell.Offset(-1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(0, -56).Select
    Else
    ActiveCell.Offset(-1, 0).Select
    End If
    Loop

    Can anyone recommend how I might adjust the syntax so I can do the full copy/paste/skip blanks process without having to actually "select" anything? Any help would be greatly appreciated!
    Last edited by jdaywalt; Apr 30th, 2013 at 09:27 PM. Reason: wrong code posted

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,003

    Default Re: VBA needed to Copy PasteSpecial Values Skip Blanks

    Replace your Do-Loop with something like this...

    Code:
        Dim i As Long
        'Loop from last used row in column O up to row 2
        Application.ScreenUpdating = False
        For i = Range("O" & Rows.Count).End(xlUp).Row To 2 Step -1
            With Range("O" & i)
                If .Value = 0 And .Value <> "" Then
                    .Offset(-1, 56).Range("A1:CA1").Value = _
                        .Offset(0, 56).Range("A1:CA1").Value
                End If
            End With
        Next i
        Application.ScreenUpdating = True
    Last edited by AlphaFrog; Apr 30th, 2013 at 10:09 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,003

    Default Re: VBA needed to Copy PasteSpecial Values Skip Blanks

    I should pay more attention to the thread titles.

    This uses the PasteSpecial-SkipBlanks method.

    Code:
        Dim i As Long
        'Loop from last used row in column O up to row 2
        Application.ScreenUpdating = False
        For i = Range("O" & Rows.Count).End(xlUp).Row To 2 Step -1
            With Range("O" & i)
                If .Value = 0 And .Value <> "" Then
                    .Offset(0, 56).Range("A1:CA1").Copy
                    .Offset(-1, 56).Range("A1:CA1").PasteSpecial Paste:=xlPasteValues, _
                                Operation:=xlNone, SkipBlanks:=True, Transpose:=False
                End If
            End With
        Next i
        Application.CutCopyMode = True
        Application.ScreenUpdating = True
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Posts
    61

    Default Re: VBA needed to Copy PasteSpecial Values Skip Blanks

    Thanks for your reply. I don't think I was clear enough with my explanation....here is my issue. Let's assume in this macro, I am copying row 20 into row 19. I need the function to "act like" the Copy ... PasteSpecial ... Value ... Skip Blanks operation. The "Skip Blanks" isn't related to column O, it has to do with individual blank cells within the copy/paste range. For example, here is what rows 19 & 20 might look like:

    -----------A --------- B---------C --------D-------- E

    19.........text1......text2

    20.......................................................text4......text5


    My macro needs to copy A20:E20 into A19:E19. The "skip blanks" is important in the paste operation because I don't want to overwrite A19 & B19 with blanks. The result of the copy from row 20 into 19 should look like this:

    -----------A---------B ---------C--------D--------E-------

    19..........text1........text2....................text4........text5



    As I said, my current macro works -- it just takes a long to perform agains 1200 rows because it has to physically select, then copy/paste, clear clipboard, repeat. Does that help with understanding what my issue is?
    Last edited by jdaywalt; Apr 30th, 2013 at 10:41 PM.

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,003

    Default Re: VBA needed to Copy PasteSpecial Values Skip Blanks

    I think I realized my error and updated the new code in post #3 as you were replying. Try the newest code in #3.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com