# Thread: set range dinamiclly (2000 sr-1)

1. ## set range dinamiclly (2000 sr-1)

how to set dinamiclly this range:

piece of code
''''''''''''''''''''''
Dim WS As Worksheet
''''''''''''''''''''

set from B2 to last cell -1 excluded cell TOT

attached sheet

2. ## 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. ## 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. ## 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

EXCELLENT!

6. ## 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)
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

7. ## 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, _