Macro to copy values then delete row for entire sheet

[Pyrotoy]

I need a macro that, when it identifies a given target phrase, will copy the
cell above the target and then delete the entire row above the target. I
would like it to do this for mulitple occurrences of the target phrase on the
entire sheet. Please help. Thanks.

 

[Shane Devenshire]

Hi,

So your macro will copy the cell and then delete the entire row. What
happened to the copied cell stuff.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Gord Dibben]

Copy the cell above and do what with it after copying?


Gord Dibben MS Excel MVP

 

[Pyrotoy]

After copying the cell above, the macro will paste it into the current active
cell. (For example, if the target phrase appears in cell A4, the macro will
first copy the contents of A4 into A5, then delete row 4. Then it will repeat
this process for each occurrence of the target phrase.) Thanks for your help.

 

[Pyrotoy]

After copying the cell above, the macro will paste it into the current active
cell. (For example, if the target phrase appears in cell A4, the macro will
first copy the contents of A4 into A5, then delete row 4. Then it will repeat
this process for each occurrence of the target phrase.) Thanks for your help.

 

[Gord Dibben]

Try this.

Sub Copy_Delete()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 2 Step -1
With Cells(X, 1)
If .Value = "myphrase" Then
.Offset(1, 0).Value = .Value
.EntireRow.Delete
End If
End With
Next X
Application.ScreenUpdating = True

End Sub


Gord

 

[Pyrotoy]

I pasted this code into the visual basic editor, but it had no effect. (I did
substitute my target phrase in the if/then statement for myphrase.) I tried
running it from the editor as well as assigning a macro keystroke
combination, but no luck. Any ideas? Thanks again.

 

[Gord Dibben]

Did you paste it into a general module?

It does what you asked for.

Copy "myphrase" cell in column A to cell below then delete the row where the
original "myphrase" was found

If "thephrase" is found in A4, that cell is copied to A5 and row 4 is
deleted.

Maybe you have not thought it out fully.

By deleting row 4, row 5 moves up to become new row 4

Test by entering in column A

a
myphrase
b
myphrase
c
myphrase
d
myphrase

Also place a sequence of numbers 1 to 8 in column B

Run the macro and you should get in column A

a
myphrase
myphrase
myphrase
myphrase

In column B

1
3
5
7
blank

Perhaps you don't want row 4 deleted...............just the contents
cleared?


Gord

 

[Pyrotoy]

I pasted it into the visual basic editor under the macro option in Tools. I
apologize, but I don't have much experience using macros and none with vb. I
did copy and paste your example from your post into a new sheet and attempted
to use the script by selecting it from the macro menu and clicking on "Run",
but nothing happened. Is there another way to invoke it?

Also, I need the macro to be able to locate the target phrase anywhere on
the sheet, not just in column A, though typically it will occur in column B.

Thanks again.

 

[Gord Dibben]

Tools>Macro>Visual Basic Editor

Hit CTRL + r to make sure you are in the Project Explorer window.

Select your workbook/project and right-click>Insert>Module.

Paste the code into that module.

There could be any number of reason why it doesn't work.

1. myphrase is part of a larger text string. If so we can deal with that.

2. myphrase is case-sensitive. We can deal with that also.

3. myphrase is not to be found in column A in any form. We can change the
code to deal with all columns.

What do you want done if myphrase is found in Column B and E in the same
row?

If you want to send me by email a copy of the workbook change the AT and DOT
to get my real email address.


Gord

 

[Pyrotoy]

Gord:

Thanks so much for your patience; I do appreciate all the time you are
spending with me.

I did as instructed, but still nothing. Do I need to also save the module
once I paste the code? When I tried to save, I was asked to save as a new
workbook: is that correct?

Regarding your questions, "myphrase" is actually " myphrase", with two
leading blank spaces. Also, this phrase will appear only in column B. It is
of mixed case, that is " My Phrase". It will not appear in more than one
column, but does occur in over 100 rows per sheet, hence my desire for a
macro.

Thanks again,
Scott

 

[Gord Dibben]

The module is saved when you save the workbook.

No, you don't need to save as a new workbook...........assuming you have
saved it at least once before.

This included macro was tested on column B with the literal " My Phrase"

Sub Copy_Delete()
Dim LastRow As Long
Dim X As Long
Dim mystr As String
mystr = " My Phrase"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 2 Step -1
With Cells(X, 2)
If .Value = mystr Then
.Offset(1, 0).Value = .Value
.EntireRow.Delete
End If
End With
Next X
Application.ScreenUpdating = True

End Sub


Gord

 

[Gord Dibben]

You may want to do a little trimming to remove the two leading spaces.

Sub Copy_Delete()
Dim LastRow As Long
Dim X As Long
Dim mystr As String
mystr = "My Phrase"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 2 Step -1
With Cells(X, 2)
If Application.Trim(.Value) = mystr Then
.Offset(1, 0).Value = Application.Trim(.Value)
.EntireRow.Delete
End If
End With
Next X
Application.ScreenUpdating = True

End Sub


Gord

 

[Gord Dibben]

I think I have steered you wrong here.

If using Excel 2007 I believe you have to save the workbook as a
macro-enabled workbook.

I don't use 2007 but I think it would be save as an *.xlsm workbook.


Gord

 

[Pyrotoy]

Gord:

I am using Excel 2003, so no worries on that. Here is a macro I tried this
morning:


Sub AutoCopyDelete()
'
' AutoCopyDelete1 Macro
' Macro recorded 12/4/2008 by Scott B. Semel
'

'
Cells.Find(What:=" Daypart Portion", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A2").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
End Sub


It will find the target phrase and perform the other operations I need. The
only solution I need now is how to make the macro perform the operation for
all instances in the entire sheet. Right now I can make that happen by
holding down the key combination until it cycles through the whole thing. But
I'd like to invoke the macro just once rather than having to hold down the
keys.

We're almost there.
Thanks again,
Scott

 

[Pyrotoy]

Gord:

I am using Excel 2003, so no worries on that. Here is a macro I tried this
morning:


Sub AutoCopyDelete()
'
' AutoCopyDelete1 Macro
' Macro recorded 12/4/2008 by Scott B. Semel
'

'
Cells.Find(What:=" Daypart Portion", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A2").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
End Sub


It will find the target phrase and perform the other operations I need. The
only solution I need now is how to make the macro perform the operation for
all instances in the entire sheet. Right now I can make that happen by
holding down the key combination until it cycles through the whole thing. But
I'd like to invoke the macro just once rather than having to hold down the
keys.

We're almost there.
Thanks again,
Scott

 

[Gord Dibben]

Did you try my latest edition of the macro we have been working on?

Looks for the phrase in column B.


Gord

 

[Pyrotoy]

Gord: Thanks for all your help. I've got it doing almost everything I need it
to. Best wishes for a merry Christmas.

Scott

 

[Gord Dibben]

Thanks for the feedback.

Merry Xmas to you and yours.


Gord