MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tidy up and sort data


Posted by Jeremy on June 02, 2000 6:08 AM

Is there a Macro that can move text to another column or columns if the 1st column of data exceeds a certain amount of rows.

Eg. I have text in column A (commencing from cell A2). in which the amount varies (could go down to Row 65336).
So if the data goes past row34 the remaining data is moved to column B (B2) until row 34 and again if any data goes past row 34 that goes to column C as so forth.

Please help.

Jeremy


Posted by Celia on June 02, 0100 7:54 AM

Jeremy
You can only do this with data in column A up to row 8449. There are not enough columns after that.

Try the following :-

Sub ThirtyFour()
If Range("A65536") <> "" Or Range("A65536").End(xlUp).Row > 8449 Then
MsgBox "There are more than 8,448 rows of data. There are not enough columns."
Exit Sub
End If
If Range("IV2") <> "" Then
MsgBox "There are no empty columns"
Exit Sub
End If
Do Until Application.CountA(Range("A35:A67")) = 0 Or Range("IV2") <> ""
With Range("A35:A67")
.Copy Range("IV2").End(xlToLeft).Offset(0, 1)
.EntireRow.Delete Shift:=xlUp
End With
Loop
End Sub

Celia


Posted by Kevin on June 02, 0100 4:22 PM

Formulas

Please help! I need a formula for a schedule-i need to use regular time not military-the only problem i have is trying to get a formula to total hours for pm to am -EX: 10 pm to 6 am--nothing seems to work! Can anyone help? Thanks

Kevin

Posted by Celia on June 02, 0100 5:34 PM

Re: Formulas

Kevin
Have a look at the info re dates at :-

http://www.cpearson.com/excel.htm

Celia