VBA needed to Copy PasteSpecial Values Skip Blanks

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
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:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Replace your Do-Loop with something like this...

Code:
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=green]'Loop from last used row in column O up to row 2[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] i = Range("O" & Rows.Count).End(xlUp).Row [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] -1
        [color=darkblue]With[/color] Range("O" & i)
            [color=darkblue]If[/color] .Value = 0 And .Value <> "" [color=darkblue]Then[/color]
                .Offset(-1, 56).Range("A1:CA1").Value = _
                    .Offset(0, 56).Range("A1:CA1").Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] i
    Application.ScreenUpdating = [color=darkblue]True[/color]
 
Last edited:
Upvote 0
I should pay more attention to the thread titles.

This uses the PasteSpecial-SkipBlanks method.

Code:
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=green]'Loop from last used row in column O up to row 2[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = Range("O" & Rows.Count).End(xlUp).Row [COLOR=darkblue]To[/COLOR] 2 [COLOR=darkblue]Step[/COLOR] -1
        [COLOR=darkblue]With[/COLOR] Range("O" & i)
            [COLOR=darkblue]If[/COLOR] .Value = 0 And .Value <> "" [COLOR=darkblue]Then[/COLOR]
                .Offset(0, 56).Range("A1:CA1").Copy
                .Offset(-1, 56).Range("A1:CA1").PasteSpecial Paste:=xlPasteValues, _
                            Operation:=xlNone, SkipBlanks:=True, Transpose:=[COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    Application.CutCopyMode = [COLOR=darkblue]True[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
 
Upvote 0
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:
Upvote 0
I think I realized my error and updated the new code in post #3 as you were replying. Try the newest code in #3.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top