Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    set range dinamiclly (2000 sr-1)

    how to set dinamiclly this range:

    piece of code
    ''''''''''''''''''''''
    Dim WS As Worksheet
    Dim rngHeader As Range
    Set rngHeader = WS.Range("B2", WS.Range("CB2"))
    ''''''''''''''''''''

    set from B2 to last cell -1 excluded cell TOT

    attached sheet

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: set range dinamiclly (2000 sr-1)

    Try this:

    Set rngHeader = WS.Range(WS.Range("B2"), WS.Range("B2").End(xlToRight).Offset(0, -1))

    WS.Range("B2").End(xlToRight) is the last filled cell to the right of B2.
    Offset(0, -1) is 1 cell to the left of this last cell.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set range dinamiclly (2000 sr-1)

    Tks for code and good explain...

    but now have the same prob for this code:
    Sub AGG_STATISTICA()

    Dim ULTIMA As String
    Dim MY_TOTAL As Double
    Dim WS As Worksheet

    Set WS = Worksheets("REPORT")

    ULTIMA = 3
    While Not Worksheets("REPORT").Range("A" & ULTIMA) = ""
    MY_TOTAL = WorksheetFunction.Sum(WS.Range("B" & ULTIMA & ":CB" & ULTIMA))
    WS.Range("CC" & ULTIMA) = MY_TOTAL
    ULTIMA = ULTIMA + 1
    Wend

    End Sub

    how to set dinamiclly cell CC and CB

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: set range dinamiclly (2000 sr-1)

    It's best to store the column number in a variable:

    Sub AGG_STATISTICA()
    Dim ULTIMA As String
    Dim MY_TOTAL As Double
    Dim WS As Worksheet
    Dim LastCol As Integer

    Set WS = Worksheets("REPORT")
    ' This will correspond to column CC.
    LastCol = WS.Range("B2").End(xlToRight).Column
    ULTIMA = 3
    While Not Worksheets("REPORT").Range("A" & ULTIMA) = ""
    MY_TOTAL = WorksheetFunction.Sum(WS.Range(WS.Cells(ULTIMA, 2), WS.Cells(ULTIMA, LastCol - 1)))
    WS.Cells(ULTIMA, LastCol) = MY_TOTAL
    ULTIMA = ULTIMA + 1
    Wend
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set range dinamiclly (2000 sr-1)

    EXCELLENT!

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set range dinamiclly (2000 sr-1)

    Fired!!!!!!!!!
    Hot to set dinamiclly the range A3:CC.... and ordering ascending by CC

    my stupid code:
    Sub ORDINA_OPERATORI()

    Dim ULTIMA As Long
    Dim ORDER_RANGE As Range

    ULTIMA = Worksheets("REPORT").Cells(65536, 1).End(xlUp).Row '+ 1

    Set ORDER_RANGE = Worksheets("REPORT").Range("A3:CC" & ULTIMA)
    ORDER_RANGE.Sort Key1:=Worksheets("REPORT").Range("CC2"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: set range dinamiclly (2000 sr-1)

    Same principle.

    Sub ORDINA_OPERATORI()
    Dim WS As Worksheet
    Dim ULTIMA As Long
    Dim ORDER_RANGE As Range
    Dim LastCol As Integer

    Set WS = Worksheets("Report")
    ULTIMA = WS.Cells(65536, 1).End(xlUp).Row '+ 1
    LastCol = WS.Cells(2, 2).End(xlToRight).Column

    Set ORDER_RANGE = WS.Range(WS.Cells(3, 1), WS.Cells(ULTIMA, LastCol))
    ORDER_RANGE.Sort Key1:=WS.Cells(3, LastCol), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set range dinamiclly (2000 sr-1)

    tKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •