The problem with the Recorder is it must encode each action you take one at
a time... it has no facilities to optimize the code after it has done this.
Perhaps this previous posting of mine will help you when you do get around
to cleaning your code up:
Whenever you see code constructed like this...
Range("A1").Select
Selection.<whatever>
you can almost always do this instead...
Range("A1").<whatever>
In your particular case, you have this...
Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows
which, using the above concept, can be reduced to this...
For Each r In Range("C2:C8193").Rows
Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.
--
Rick (MVP - Excel)
"covingj" <(E-Mail Removed)> wrote in message
news:09727A35-7C09-4F78-906C-(E-Mail Removed)...
> Thanks, that worked like a charm.
>
> As far as the code, that's what you get when you let Microsoft record a
> macro for you. It is indented to make it a little easier on the eyes, but
> the
> formating did not carry over when I pasted. When I have time, I'll try to
> clean it up like you suggested. Of course it would help if I understood
> what
> I was doing and not just making it up as I went, LOL.
>
> Thanks again.
>
> "Dave Peterson" wrote:
>
>> If you know the name of the textbox:
>> Worksheets("BG Info").TextBox1.Value = ""
>>
>> ps.
>>
>> This code:
>>
>> > Sheets("FI Resources").Select
>> > Range("Clear_FI_Resource").Activate
>> > Selection.ClearContents
>> > Range("C8").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>>
>> could be rewritten as:
>>
>> Sheets("FI Resources").Range("Clear_FI_Resource").clearcontents
>> sheets("FI Resources").Range("C8").value = 1500
>>
>> This kind of thing will make the code run a bit faster, but even better,
>> it's
>> easier to understand.
>>
>> covingj wrote:
>> >
>> > I have an Active X text box in an Excel workbook that I would like to
>> > be able
>> > to clear the contents of the box using a macro. Currently the workbook
>> > uses a
>> > macro to clear the contents of cells to prepare it for use. I would
>> > like to
>> > add instructions to this exiting macro to also clear the text box being
>> > used
>> > to enter notes.
>> >
>> > Below is the macro. The workbook has multiple sheets, but the text box
>> > only
>> > appear on the BG Info sheet. That section appears at teh end of the
>> > Macro.
>> >
>> > Thanks for any input.
>> >
>> > Sub Clear_All()
>> > '
>> > ' Clear_All Macro
>> > ' Macro recorded 03/03/2005 by covingj
>> > '
>> > '
>> > Sheets("FI Resources").Select
>> > Range("Clear_FI_Resource").Activate
>> > Selection.ClearContents
>> > Range("C8").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>> > Range("B2:C2").Select
>> > Sheets("PW").Select
>> > Range("Clear_PW").Activate
>> > Selection.ClearContents
>> > Range("F3").Select
>> > Sheets("Preg Minor").Select
>> > Range("Clear_Preg_Minor").Activate
>> > Selection.ClearContents
>> > Range("B7:C7").Select
>> > Sheets("FP").Select
>> > Range("Clear_FP").Activate
>> > Selection.ClearContents
>> > Range("F2").Select
>> > Sheets("LIF").Select
>> > Range("Clear_LIF").Activate
>> > Selection.ClearContents
>> > Range("N2").Select
>> > Sheets("TMA").Select
>> > Range("Clear_TMA").Activate
>> > Selection.ClearContents
>> > Range("F2").Select
>> > Sheets("HCPC").Select
>> > Range("Clear_HCPC").Activate
>> > Selection.ClearContents
>> > Range("F2").Select
>> > Sheets("ABD-SLMB").Select
>> > Range("C7").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>> > Range("Clear_ABD").Activate
>> > Selection.ClearContents
>> > Range("G2").Select
>> > Sheets("QI").Select
>> > Range("C7").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>> > Range("Clear_QI").Activate
>> > Selection.ClearContents
>> > Range("G2").Select
>> > Sheets("OSS").Select
>> > Range("Clear_OSS").Activate
>> > Selection.ClearContents
>> > Range("C7").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>> > Range("F2").Select
>> > Sheets("NH-HCBS").Select
>> > Range("E7").Select
>> > ActiveCell.FormulaR1C1 = "1500"
>> > Range("Clear_NH").Activate
>> > Selection.ClearContents
>> > Range("J28").Select
>> > ActiveCell.FormulaR1C1 = "=R[-6]C"
>> > Range("I2:J2").Select
>> > Sheets("IT").Select
>> > Range("Clear_IT").Activate
>> > Selection.ClearContents
>> > Range("C16").Select
>> > ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
>> > Range("F16").Select
>> > ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
>> > Range("C7").Select
>> > With ActiveCell.Characters(Start:=1, Length:=1)
>> > End With
>> > Range("C7").Select
>> > Sheets("BG Info").Select
>> > Range("Clear_BG_Info").Activate
>> > Selection.ClearContents
>> > Range("Primary").Select
>> > End Sub
>>
>> --
>>
>> Dave Peterson
>>
|
Similar Threads
