Delete Cells

[thepartydj]

I have a file thats like this:

Column one Column Two Colum Three
Info in row one
Info in row two
Info in row three
Info in row four
Info in row five
Info in row six


I want to line up all the information easily. Is there an easy way to do this?

 

[hiker95]

thepartydj,

We can not tell exactly what you are trying to do.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[thepartydj]

Thank you....

Here is what I want to do. As you can see these are all address. I am trying to convert them all from word to excel. I need to put these in line with each other. Any ideas? I have about 2000 lines to do this to. Thanks

Excel Workbook
	A	B	C	D	E	F
1	Aman, Louella
2				405 Adams Ave.
3					Volga
4						605-555-5685
5	Anderson, Melissa & Matt
6		Maquenzie, Montana
7				702 E. 5th St.
8					Volga
9						605-555-6543
Sheet1

Excel 2003

 

[hiker95]

thepartydj,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Aman, Louella
2				405 Adams Ave.
3					Volga
4						605-555-5685
5	Anderson, Melissa & Matt
6		Maquenzie, Montana
7				702 E. 5th St.
8					Volga
9						605-555-6543
10
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Aman, Louella							Aman, Louella			405 Adams Ave.	Volga	605-555-5685
2				405 Adams Ave.				Anderson, Melissa & Matt	Maquenzie, Montana		702 E. 5th St.	Volga	605-555-6543
3					Volga
4						605-555-5685
5	Anderson, Melissa & Matt
6		Maquenzie, Montana
7				702 E. 5th St.
8					Volga
9						605-555-6543
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub ShiftDataUp()
' hiker95, 05/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=550506
Dim LR As Long, i As Long, ii As Long, o As Long
Dim Iary, Oary
Application.ScreenUpdating = False
LR = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Iary = Range("A1:F" & LR).Value
ReDim Oary(1 To Application.CountA(Columns(1)), 1 To 6)
o = 0
For i = LBound(Iary) To UBound(Iary)
  If Iary(i, 1) <> "" Then
    o = o + 1
    Oary(o, 1) = Iary(i, 1)
  End If
  For ii = 2 To 6 Step 1
    If Iary(i, ii) <> "" Then
      Oary(o, ii) = Iary(i, ii)
      Exit For
    End If
  Next ii
Next i
Range("H1").Resize(UBound(Oary), 6).Value = Oary
Columns("H:M").AutoFit
Erase Iary
Erase Oary
Application.ScreenUpdating = True
End Sub

Then run the ShiftDataUp macro.

 

[thepartydj]

Thank you very much, that worked great!