Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move cell contents with macro | Excel Discussion (Misc queries) | |||
macro to move cell contents and then delete the row | Excel Programming | |||
macro to move cell contents and then delete the row | Excel Programming | |||
Macro to move the contents of a cell | Excel Programming | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) |