macro to move and delete contents
 

If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

#1 statement is correct. But for #2, I'm not sure what you are asking. If I
am understanding you correctly you are asking does something like the
following exist:

"Column A"
Data1*
* Example1
* Example1.1
* Example1.2

and so on. If that is what you are wanting to know, the answer is no. It
either is:

Data1

or

Data1*
* Example1








"Ron de Bruin" wrote:

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

Ok Mark

I create a tester for you after dinner

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
#1 statement is correct. But for #2, I'm not sure what you are asking. If I
am understanding you correctly you are asking does something like the
following exist:

"Column A"
Data1*
* Example1
* Example1.1
* Example1.2

and so on. If that is what you are wanting to know, the answer is no. It
either is:

Data1

or

Data1*
* Example1








"Ron de Bruin" wrote:

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

Try this one Mark

Sub Testing3()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 1 To lr Step 1
If Left(Trim(Cells(I, 1)), 1) = "*" Then
Cells(I, 1).Cut Cells(I - 1, 3)
End If
Next I

On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Ok Mark

I create a tester for you after dinner

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
#1 statement is correct. But for #2, I'm not sure what you are asking. If I
am understanding you correctly you are asking does something like the
following exist:

"Column A"
Data1*
* Example1
* Example1.1
* Example1.2

and so on. If that is what you are wanting to know, the answer is no. It
either is:

Data1

or

Data1*
* Example1








"Ron de Bruin" wrote:

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

That worked like a charm!

Thank you very much Ron.



"Ron de Bruin" wrote:

Try this one Mark

Sub Testing3()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 1 To lr Step 1
If Left(Trim(Cells(I, 1)), 1) = "*" Then
Cells(I, 1).Cut Cells(I - 1, 3)
End If
Next I

On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Ok Mark

I create a tester for you after dinner

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
#1 statement is correct. But for #2, I'm not sure what you are asking. If I
am understanding you correctly you are asking does something like the
following exist:

"Column A"
Data1*
* Example1
* Example1.1
* Example1.2

and so on. If that is what you are wanting to know, the answer is no. It
either is:

Data1

or

Data1*
* Example1








"Ron de Bruin" wrote:

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3

macro to move and delete contents
 

You are welcome


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
That worked like a charm!

Thank you very much Ron.



"Ron de Bruin" wrote:

Try this one Mark

Sub Testing3()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 1 To lr Step 1
If Left(Trim(Cells(I, 1)), 1) = "*" Then
Cells(I, 1).Cut Cells(I - 1, 3)
End If
Next I

On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Ok Mark

I create a tester for you after dinner

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
#1 statement is correct. But for #2, I'm not sure what you are asking. If I
am understanding you correctly you are asking does something like the
following exist:

"Column A"
Data1*
* Example1
* Example1.1
* Example1.2

and so on. If that is what you are wanting to know, the answer is no. It
either is:

Data1

or

Data1*
* Example1








"Ron de Bruin" wrote:

1) There is a * before each line that you want to move up and to column C

2) Is there always one line with a * between the Data lines

Am I correct with one and two


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
Ron,

Here is a minor modification to what the data looks like:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3
Data4
Data5*
* Example5

I was wanting just the data in the columns (* Example()) to move up and
then delete that row. Basically I have data that I copied from a text file
into Excel, however some of the data used a line continuation, so I wanted to
move that data to the same line as the previous so it is easier to do Text to
Columns and then Sort the data. The final product would look like this:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3
Data4
Data5* * Example5

So basically I would like it to search for (* ) (a star with spaces after
it since the data (Example) is different).

Thank you



"Mark64" wrote:

Ron,

Sorry, I was just trying to run your macro. I received an error yesterday
when I tried to submit it and so I tried to post it again, althought it had
already posted.
I'll let you know.

"Ron de Bruin" wrote:

Have you try my code example I posted yesterday ?

You can change it to copy to C instead of B

I change
Cells(I, 1).Cut Cells(I - 1, 2)
To
Cells(I, 1).Cut Cells(I - 1, 3)


Sub Testing2()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To lr Step 2
Cells(I, 1).Cut Cells(I - 1, 3)
Next I
On Error Resume Next 'In case there are no blank cells
Range(Cells(1, 1), Cells(lr, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark64" wrote in message ...
If I have a set of data located in the first column similar as follows:

"Column A" "Column B" "Column C"
Data1*
* Example1
Data2*
* Example2
Data3*
* Example3

and so on..

Is it possible to create a macro to go through and search that column of
data and and move the contents from Column A to Column C and up one row as
follows:

"Column A" "Column B" "Column C"
Data1* * Example1

Data2* * Example2

Data3* * Example3

Then delete the row that the data was in so the final product is as follows:

"Column A" "Column B" "Column C"
Data1* * Example1
Data2* * Example2
Data3* * Example3