|
|
#1
06-04-2017, 07:15 AM
|
|||
|
|||
Move text to another cell
I have several blocks of text like this...
A1: coffee A2: creamer A4: straws A5: cups ...I need to be able to move each second row of text to the cell one row above and one column right of it and remove the empty rows between so that it looks like this... A1: coffee B1: creamer A2: straws B2: cups Is there a macro that can help me do this through the whole spreadsheet? Thanks Last edited by ewso; 06-04-2017 at 04:04 PM. 
|
|
#3
06-04-2017, 03:51 PM
|
|||
|
|||
|
Quote:
A1 A2 A4 A5 A7 A8 I need it to be like... A1 B1 A2 B2 A3 B3 Thanks
|
|
#4
06-05-2017, 07:59 AM
|
|||
|
|||
|
Maybe something along the lines of this...
Code:
With Sheets("Sheet1")
'get last row of col A
lr = .Cells(Rows.Count, "A").End(xlUp).Row
'move second value
For i = 1 To lr Step 3
.Cells(i, 2).Value = .Cells(i + 1, 1).Value
Next i
'remove rows where B is blank
.Range("B1:B" & lr).SpecialCells(xlBlanks).EntireRow.Delete
End With
|
|
#5
06-05-2017, 09:55 AM
|
|||
|
|||
|
Quote:
|
|
#6
06-05-2017, 10:56 AM
|
|||
|
|||
|
Hi,
Sorry I have not responded to you. But, NoSparks code does work for me. However, if you have a "Header" it will not work as you want. I modified Nosparks code to this. Hope NoSparks does not mind. Code:
Sub nn()
With Sheets("Sheet1")
'get last row of col A
lr = .Cells(Rows.Count, "A").End(xlUp).Row
'move second value
For i = 2 To lr Step 3
.Cells(i, 2).Value = .Cells(i + 1, 1).Value
Next i
'remove rows where B is blank
.Range("B2:B" & lr).SpecialCells(xlBlanks).EntireRow.Delete
End With
End Sub
|
|
#7
06-05-2017, 12:27 PM
|
|||
|
|||
|
Charles thanks...I was able to get the macro to run after you changed it, but it's not working right. Instead of making..
A1: coffee A2: creamer A4: straws A5: cups Look like this.. A1: coffee B1: creamer A2: straws B2: cups It's doing this.. A1: creamer B3: coffee A2: cups B4: straws ..All the text from the second rows are only in column A and all the text from the first rows are in column B down the spreadsheet.
|
|
#8
06-05-2017, 02:10 PM
|
|||
|
|||
|
And when I run NoSparks code after adding the header, it works well, but only for about the first half of the spreadsheet. Then the last half of the spreadsheet removes the text from the first rows completely. The spreadsheet then looks like this...
A1: coffee B1: creamer A2: cups A3: etc. What could be making the script work for about half of the spreadsheet but not the whole thing?
|
|
#10
06-05-2017, 07:37 PM
|
|||
|
|||
|
NoSparks, it works! One of the lines about halfway through the spreadsheet didn't have a space between it and the row above it and it's what was making the macro fail, but now it works just like I needed.
Thanks guys!
|
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| extract time from a cell and move it to front of cell before text | ewso | Excel | 20 | 03-19-2017 11:34 AM |
| Move the data at bottom cell to right side of the cell | kcyag91 | Excel | 1 | 01-28-2016 12:28 AM |
 Macro to move focus after entry in a cell
|
Phil H | Excel Programming | 3 | 06-18-2015 01:20 PM |
| Move data from 1 cell to another cell | Catalin.B | Excel | 1 | 06-25-2011 12:51 PM |
| Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |
Windows 10 
Office 2016
Linear Mode
