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!
* 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: