Different approach .... try this:

Sub CopyRows()
Dim LastRow As Long
Dim i As Long
Dim x As Integer
Dim NewRows As Integer
Application.ScreenUpdating = False
LastRow = Range("H65536").End(xlUp).Row
For i = LastRow To 1 Step -1
If Len(Range("H" & i)) 65 Then
NewRows = Int(Len(Range("H" & i)) / 65)
For x = NewRows To 1 Step -1
Range("H" & i).Offset(1, 0).EntireRow.Insert
Range("A" & i & ":G" & i).Copy _
Range("A" & (i + 1))
Range("H" & (i + 1)) = _
Mid(Range("H" & i), (x * 65) + 1, 65)
Next ' NewRows
Range("H" & i) = Left(Range("H" & i), 65)
End If
Next ' i
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Nat1" wrote in message
...
Hi,

Can someone please help me with a macro which was kindly written by
another
member ). What I am trying to accomplish is to cut text
from col H 65 char and inserting into the next row in col H.

Lyford's code does just that, but what I also need is cols A:G (which are
primary keys) to be copied and inserted onto the next row. This should
only
happen when col H is cut and shift down.

I'm working with an oracle database (relational) and the field character
limit is 65. My spreadsheet has over 10,000 records, so this macro would
be
invaluable.

Can someone tell me what I need to change in this macro:

Sub TrimTo65()

myRow = 1
Range("H" & myRow).Select
myString = ActiveCell.Value

While myString < ""

' Walk the column, as long as you don't encounter empty cells.

While Len(myString) 65

' If the string is fewer than 65 characters, no work is required.
If more, we split it up...

' and shove the next 65 characters into the current cell.

mySubString = Left(myString, 65)
ActiveCell.Value = mySubString

' ...and adjust the string

myString = Right(myString, Len(myString) - 65)

' shift everything down, to open a new cell for the remainder of
this text


myRow = myRow + 1
Range("H" & myRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

If Len(myString) < 65 Then
' OK - the remainder is fewer than 65 characters. Stick it
into
the opened cell, and move on.

ActiveCell.Value = myString

End If


Wend

' advance to the next cell

myRow = myRow + 1
Range("H" & myRow).Select
myString = ActiveCell.Value

Wend

End Sub


Any help, suggestions would be greatly appreciated. I'm at my wits end
trying to figure out what I'm doing wrong. I will be honest I'm a novice
with
visual basic, I have only just started to write in this language.

Regards,

Nat

You're very welcome. Thanks for the feedback.

Regards

Trevor


"Nat1" wrote in message
...
Trevor,

I am so impressed! 10,000 or so rows updated in around 15 secs!! I really
need to Familiarize myself with vba. I have only been using the language
for
about 2 months.

Thank-you so much for your help, you have saved me an enormous amount of
time.
The Microsoft discussion forum really is an invaluable resource.

Thanks again

Nat

"Trevor Shuttleworth" wrote:

Different approach .... try this:

Sub CopyRows()
Dim LastRow As Long
Dim i As Long
Dim x As Integer
Dim NewRows As Integer
Application.ScreenUpdating = False
LastRow = Range("H65536").End(xlUp).Row
For i = LastRow To 1 Step -1
If Len(Range("H" & i)) 65 Then
NewRows = Int(Len(Range("H" & i)) / 65)
For x = NewRows To 1 Step -1
Range("H" & i).Offset(1, 0).EntireRow.Insert
Range("A" & i & ":G" & i).Copy _
Range("A" & (i + 1))
Range("H" & (i + 1)) = _
Mid(Range("H" & i), (x * 65) + 1, 65)
Next ' NewRows
Range("H" & i) = Left(Range("H" & i), 65)
End If
Next ' i
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Nat1" wrote in message
...
Hi,

Can someone please help me with a macro which was kindly written by
another
member ). What I am trying to accomplish is to cut
text
from col H 65 char and inserting into the next row in col H.

Lyford's code does just that, but what I also need is cols A:G (which
are
primary keys) to be copied and inserted onto the next row. This should
only
happen when col H is cut and shift down.

I'm working with an oracle database (relational) and the field
character
limit is 65. My spreadsheet has over 10,000 records, so this macro
would
be
invaluable.

Can someone tell me what I need to change in this macro:

Sub TrimTo65()

myRow = 1
Range("H" & myRow).Select
myString = ActiveCell.Value

While myString < ""

' Walk the column, as long as you don't encounter empty cells.

While Len(myString) 65

' If the string is fewer than 65 characters, no work is
required.
If more, we split it up...

' and shove the next 65 characters into the current cell.

mySubString = Left(myString, 65)
ActiveCell.Value = mySubString

' ...and adjust the string

myString = Right(myString, Len(myString) - 65)

' shift everything down, to open a new cell for the remainder of
this text


myRow = myRow + 1
Range("H" & myRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

If Len(myString) < 65 Then
' OK - the remainder is fewer than 65 characters. Stick it
into
the opened cell, and move on.

ActiveCell.Value = myString

End If


Wend

' advance to the next cell

myRow = myRow + 1
Range("H" & myRow).Select
myString = ActiveCell.Value

Wend

End Sub


Any help, suggestions would be greatly appreciated. I'm at my wits end
trying to figure out what I'm doing wrong. I will be honest I'm a
novice
with
visual basic, I have only just started to write in this language.

Regards,