Drop the cleaning up/sorting the cells until you're done with all the data
entry.

Make sure that events are disabled before you write to the worksheet.

Turn calculation off until you're almost done.

Minitman wrote:

Greetings,

I have a UserForm with 41TextBoxes on it. I am using the Offset
method to transfer the value of each box to the last row of the column
that the data belongs in. I then clear cells with only 0's in them.
And last I sort the sheet. This is taking about a minute each cycle
and getting longer.

Here is the code that I use:

Set ExtWB2 = Workbooks("1993-12.xls")
Set ExtWS2 = ExtWB2.Worksheets("Customers")

ExtWS2.Activate

With Range("A65536").End(xlUp).Offset(1, 0)
.Value = CB1.Text
.Offset(0, 2).Value = TB2.Value
.Offset(0, 3).Value = TB3.Value
<snip 4 - 40
.Offset(0, 41).Value = TB41.Value
End With

Cells.Select
'This looks for cells with only one 0 in them and clears them
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
:=False, ReplaceFormat:=False
'This sorts the sheet by the A column
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal, DataOption3:=xlSortNormal
Range("A1").Select

It is taking a minute to cycle one name. I have about 600 to do and
that is going to take a LONG time to finish.

Anyone have any ideas as to how to increase the speed of this sub?

TIA

-Minitman

--

Dave Peterson