[dba-Tech] OT - Excel VBA Cell Selection Syntax (x-posted)

Hi Tina,

Thanks for your help!  I haven't seen a Union function in Excel before.

Dan Waters

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Thursday, August 18, 2005 8:58 AM
To: Discussion of Hardware and Software issues
Subject: Re: [dba-Tech] OT - Excel VBA Cell Selection Syntax (x-posted)

Hi Dan,
I have an Excel workbook used for some automatic calculations for 
customer jobs, the completed first sheet is printed off as a PDF file, 
then the first sheet is cleared of all entries.  The ranges to be 
cleared are all over that sheet; there are a few that are contiguous, 
but several that are non-adjacent.  All my ranges are named.  What has 
worked for me is to dim each range separately, and dim a union range - 
like this:

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, r6 
As Range, r7 As Range, r8 As Range, allBodyInfo As Range

    Sheets("EntrySheet").Activate
    Set r1 = Range("CustName", "CustEmail")
    Set r2 = Range("Constr", "Condition")
    Set r3 = Range("RailEdge")
    Set r4 = Range("RailEdgeExplain")
    Set r5 = Range("WheelWells")
    Set r6 = Range("WheelWellsExplain")
    Set r7 = Range("Entry_A", "Entry_D")
    Set r8 = Range("Entry_IFH", "Entry_FW")
    Set r9 = Range("Entry_1B", "Entry_5B")
    Set r10 = Range("EntryDoor_IFH", "Entry_DDM")
    Set r11 = Range("Entry_HDWC")
    Set r12 = Range("Entry_TrkMfr", "Entry_TrkCond")
    Set allBodyInfo = Union(r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, 
r11, r12)
    allBodyInfo.Select
    Selection.ClearContents

Hope this is a helpful to you - I so seldom get to help around here, and 
I am so often helped.

Tina

Dan Waters wrote:

>Yahoooo!
>
>That worked like a charm!
>
>Also - you called this refactoring.  I've heard that term before - what
does
>it mean?
>
>Dan Waters
>
>
>-----Original Message-----
>From: dba-tech-bounces at databaseadvisors.com
>[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Shamil
>Salakhetdinov
>Sent: Tuesday, August 16, 2005 3:53 PM
>To: Discussion of Hardware and Software issues
>Subject: Re: [dba-Tech] OT - Excel VBA Cell Selection Syntax (x-posted)
>
>Dan,
>
>Both work OK here with MS Excel 2003 when ("Available Items") is active
>worksheet.
>I think you need to refactor your code to have:
>
>Dim wks As Excel.Worksheet
>    Set wks = Worksheets("Available Items")
>    wks.Select
>    wks.Range(wks.Cells(2, 1), wks.Cells(123, 2)).Select
>
>HTH,
>Shamil
>--
>Web: http://smsconsulting.spb.ru/shamil_s
>
>----- Original Message ----- 
>From: "Dan Waters" <dwaters at usinternet.com>
>To: "DBA-Tech" <dba-tech at databaseadvisors.com>; "AccessD"
><accessd at databaseadvisors.com>
>Sent: Tuesday, August 16, 2005 6:47 PM
>Subject: [dba-Tech] OT - Excel VBA Cell Selection Syntax (x-posted)
>
>
>  
>
>>Hello to All,
>>
>>I'm struggling with syntax for the selection of cells when trying to use
>>    
>>
>row
>  
>
>>and column indexes.  Below is an example:
>>
>>Example A:
>>    Worksheets("Available Items").Range("A2:B123").Select
>>
>>Example B:
>>    Worksheets("Available Items").Range(Cells(2, 1), Cells(123, 2)).Select
>>
>>Example A works fine, but B does not.  Both are from Excel Help files (see
>>the Range Property).  Once this works I will substitute a variable for the
>>123 value.
>>
>>Thanks!
>>Dan Waters
>>
>>_______________________________________________
>>dba-Tech mailing list
>>dba-Tech at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-tech
>>Website: http://www.databaseadvisors.com
>>    
>>
>
>_______________________________________________
>dba-Tech mailing list
>dba-Tech at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-tech
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>dba-Tech mailing list
>dba-Tech at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-tech
>Website: http://www.databaseadvisors.com
>
>  
>

_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com