Move cell to new cell in same sheet w/condtio

Microsoft Microsoft excel 2007 full vers...
October 21, 2010 at 10:59:53
Specs: Windows XP, 2Ghz/2GB Ram
I have a report that I need to reformat. Just at the last remaining step and wanted to see if there was a way to do this via a macro. Here is the structure: (note want to move the data within the same sheet)

Host: is in Cell A1 and Servername is in Cell B1
Next row is Cell A2 is blank and Cell B2 is Number of LUNs

What I want to do is move Host: down 1 cell and move the Servername below where the Host: was moved to. This needs to be repeated for every occurrence where all conditions exists. Servername is above Number of LUNs, Host: is to the left of Servername, and there is data above Number of LUNs (since there are some instances where there is no data above Number of LUNs.

See More: Move cell to new cell in same sheet w/condtio

October 21, 2010 at 11:23:29
Using the following instructions, please post a short table that shows what your current report looks like and then what you would like to look like when you are done "reformatting".

Since we can't see your spreadsheet from where we're sitting, it's a little difficult to offer a solution.

To line up your data in your post, please use the pre tags found above the Reply box.

1 - Click the pre icon found above the Reply box.
2 - Enter your data between the tags.
3 - Click Preview Follow Up to see if you like the way it looks.
4 - If you need to fix the layout, fix it in the Message box below the Preview box.
5 - Click the "Check To Show Confirmation Page Again" box.
6 - Click either Confirm button to Preview the post again.

Repeat steps 4 – 6 as often as necessary until you like the way the post looks and then click Confirm.

Report •

October 21, 2010 at 11:48:56
Here is one version, the rest do not have the missing Host:. So for this one the 12 would be added to the 526 and delete the:

Host: 	server8
	Number of LUNs
LDEV	Label
	Number of LUNs
LDEV	Label

So it should look like:
Host: 	Number of LUNs
server8	536
LDEV	Label

Report •

October 21, 2010 at 12:46:29
re: Here is one version, the rest do not have the missing Host:.

For the one offs, why not just fix them manually?

It would probably take longer to write the code than to just fix them yourself, unless you have a huge number of them.

Once that is fixed and is set up like all the others that you want to reformat, this code should do it.

Note: I suggest you run this code in a backup copy of your workbook. Macros can not be undone and this code will delete lines.

It assumes that there is nothing that you need to the right of Hosts: server_name since it deletes those rows after moving the strings down.

Sub MoveHostServerName()
'Determine last row with data in Column A
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Start at bottom of list:
   For rw = lastRow To 1 Step -1
'Each time we find the string "Host:" put "Host:"
'in the cell below it, copy the server name below that
'and then delete the "Host: Server Name" row
   If Cells(rw, 1) = "Host:" Then
     Cells(rw + 1, 1) = "Host:"
     Cells(rw + 2, 1) = Cells(rw, 2)
    End If
End Sub

Report •

Related Solutions

October 21, 2010 at 14:10:49
Thanks DerbyDad03, this works great. As for the others, there is not that many and I can find a way to get those taken care of.I appreciate your quick turn around.

Report •

Ask Question