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

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

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman



On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
wrote:

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

Application.enableevents = false
'do a bunch of stuff
application.enableevents = true

This stops those worksheet_change events (among others) from firing.

Minitman wrote:

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman

On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
wrote:

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

Hey Dave,

I just tried it. It took 1:09 without the events disabled and 1:11
with events disabled(?).

Is there a more efficient way to get the data from the UserForm to the
worksheet?

This is getting very frustrating!

Any ideas on alternatives are most appreciated.

TIA

-Minitman



On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
wrote:

Application.enableevents = false
'do a bunch of stuff
application.enableevents = true

This stops those worksheet_change events (among others) from firing.

Minitman wrote:

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman

On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
wrote:

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

This code shouldn't take a minute to execute:

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

Well, maybe the snipped portion does more than you want.

Minitman wrote:

Hey Dave,

I just tried it. It took 1:09 without the events disabled and 1:11
with events disabled(?).

Is there a more efficient way to get the data from the UserForm to the
worksheet?

This is getting very frustrating!

Any ideas on alternatives are most appreciated.

TIA

-Minitman



On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
wrote:

Application.enableevents = false
'do a bunch of stuff
application.enableevents = true

This stops those worksheet_change events (among others) from firing.

Minitman wrote:

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman

On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
wrote:

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