Hello,

I have a spreadsheet with a bunch of data in column A. I would like to

have a macro that starts at row 1, searches for predetermined sets of
text in A, and if found, cuts/pastes that row of text one cell up and
to the right.


Example.


Column A


Title
name
data
name
data
Title
name
test
name
test
name
data
Title


Each 'data' row and each 'test' row correspond with the 'name' above
it. Cut each 'data' row found and paste it into the the corresponding
'name' row (one above, one column over (column B)). Cut each 'name'
row found and paste it into the corresponding 'name' row (one row up
but column B).


** 'data' and 'test' rows contain other text as well, but cell will
"start" with either 'data' or 'test'. **


Rows will vary - not necessarily 'every other' because there are some
'titles' mixed in intermittently.


Any help would be appreaciated.


Thank you!

try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

That is exactly what is happening.

If the cell contains only 'test' or 'data', the macro works perfectly.
However, the cells only begin with 'test' or 'data' and have other
words after them - ex. "Data no 1".

Any way to make this macro work with an IF/Then that if x.value
CONTAINS or BEGINS with 'test' or 'data' instead of equals?


Thanks again.


n412 wrote:
Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

ok try

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value Like "test*" Or x.Value Like "data*" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub


"n412" skrev:

That is exactly what is happening.

If the cell contains only 'test' or 'data', the macro works perfectly.
However, the cells only begin with 'test' or 'data' and have other
words after them - ex. "Data no 1".

Any way to make this macro work with an IF/Then that if x.value
CONTAINS or BEGINS with 'test' or 'data' instead of equals?


Thanks again.


n412 wrote:
Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

Thank you - that did the trick.

A couple more questions, I am searching for about 15 values through
10000 lines of code. It takes about 4 minutes or so for the macro to
run. Is that normal, or is there a different way I should be doing
this based on the number of values I'm searching for.

Also, is there a way to take a different action (delete the row) for
two particular values if found - like an "Else If" statement?




excelent wrote:
ok try

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value Like "test*" Or x.Value Like "data*" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub


"n412" skrev:

That is exactly what is happening.

If the cell contains only 'test' or 'data', the macro works perfectly.
However, the cells only begin with 'test' or 'data' and have other
words after them - ex. "Data no 1".

Any way to make this macro work with an IF/Then that if x.value
CONTAINS or BEGINS with 'test' or 'data' instead of equals?


Thanks again.


n412 wrote:
Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

oh my that was alot of rows :-)
try this for speed up

Sub Move()
Application.ScreenUpdating = False
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value Like "test*" Or x.Value Like "data*" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub


"n412" skrev:

Thank you - that did the trick.

A couple more questions, I am searching for about 15 values through
10000 lines of code. It takes about 4 minutes or so for the macro to
run. Is that normal, or is there a different way I should be doing
this based on the number of values I'm searching for.

Also, is there a way to take a different action (delete the row) for
two particular values if found - like an "Else If" statement?




excelent wrote:
ok try

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value Like "test*" Or x.Value Like "data*" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub


"n412" skrev:

That is exactly what is happening.

If the cell contains only 'test' or 'data', the macro works perfectly.
However, the cells only begin with 'test' or 'data' and have other
words after them - ex. "Data no 1".

Any way to make this macro work with an IF/Then that if x.value
CONTAINS or BEGINS with 'test' or 'data' instead of equals?


Thanks again.


n412 wrote:
Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

Thank you - that did the trick.

A couple more questions, I am searching for about 15 values through
10000 lines of code. It takes about 4 minutes or so for the macro to
run. Is that normal, or is there a different way I should be doing
this based on the number of values I'm searching for.

Also, I've got a bunch of blank rows in my spreadsheet I'd like to
delete. I tried adding as ElseIf x.Value = "" Then x.EntireRow.Delete,
but that did not get rid of the rows.





excelent wrote:
ok try

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value Like "test*" Or x.Value Like "data*" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub


"n412" skrev:

That is exactly what is happening.

If the cell contains only 'test' or 'data', the macro works perfectly.
However, the cells only begin with 'test' or 'data' and have other
words after them - ex. "Data no 1".

Any way to make this macro work with an IF/Then that if x.value
CONTAINS or BEGINS with 'test' or 'data' instead of equals?


Thanks again.


n412 wrote:
Thank you for the response.

I tried the code, and all it seems to do is select all the cells in
Column A that contain data. Nothing is getting cut/pasted.

Not sure if this has anything to do with it, but the cells that contain
"test" or "data" contain other text as well - but they do begin with
"test" or "data".


Thank you.


excelent wrote:
try this,- remember to make backup first right :-)

Sub Move()
Range(("A1"), Range("A65500").End(xlUp)).Select
For Each x In Selection
If x.Value = "test" Or x.Value = "data" Then
x.Copy Destination:=x.Offset(-1, 1)
x.EntireRow.Delete
End If
Next
End Sub

delete emty rows:

select A1
press and hold CTRL+SHIFT
pull down-arrow until all rows with values is selected
if u reach A65536 then pull up-arrow onse

now press F5 and click on Special
check out Emty cells
click ok

right click and select Delete