macro to move and delete contents

[Guest]

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

 

[Ron de Bruin]

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.Delete
On Error GoTo 0
End Sub

 

[Guest]

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.

 

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

#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]

Ok Mark

I create a tester for you after dinner

 

[Ron de Bruin]

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.Delete
On Error GoTo 0

End Sub

 

[Guest]

That worked like a charm!

Thank you very much Ron.

 

[Ron de Bruin]

You are welcome