Copy paste special values macro

[dsalmons]

Hi All,

I'm after a bit of help!

I have an excel speradsheet with columns A to AB populated with a mix of data and lookups. what I would like to do is have a macro that would look at cell AB1 and if an "X" is populated it would copy the row and paste it as values then look for the next "X" in column AB and repeat the paste special.

By doing this I'm hoping to reduce the overall file size.

Thanks for looking

Daron

 

[elmer007]

I believe the following macro will get you to what you're after, but let me know if it doesn't. Also, PLEASE NOTE that you cannot click undo after running the code, so you may want to make a backup file before you start testing.

Click in cell AB1 before running this macro. Also, this will check through 10,000 rows for X's. If you need more, then change the 10000 in the code to however many rows you need (or let me know some other condition that could indicate when to stop running).

Also, capitalization matters for the X's. This finds capital X only. A better solution may be to tag each row in column AB with a 1 instead of an X (if you do, then change the "X" to 1 in the code).

If ActiveCell.Column <> 28 Then
    MsgBox ("Select cell AB1 to begin this macro.")
    Exit Sub
Else
    If ActiveCell.Row <> 1 Then
        MsgBox ("Select cell AB1 to begin this macro.")
    Else
        Do Until ActiveCell.Row = 10000
        If ActiveCell.Value = "X" Then
            ActiveCell.EntireRow.Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 28 - ActiveCell.Column).Select
        End If
        ActiveCell.Offset(1, 0).Select
        Loop
    End If
End If
End Sub

If this code is too slow, it can be sped up. I'm just checking to see if it does what you're looking for at first.

 

[elmer007]

Just noticed that I didn't copy the first line of code:

Sub Macro1()

Goes one line above the "If ActiveCell.Column <> 28 Then" line...