Nat1
12/14/2006 10:36:00 PM
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" <Nat1@discussions.microsoft.com> wrote in message
> news:7E8EE3B4-A187-4116-8369-24A52CDDCB53@microsoft.com...
> > Hi,
> >
> > Can someone please help me with a macro which was kindly written by
> > another
> > member (LyfordIII@aol.com). 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,
>
>
>