Setting a variable range for a custom sort.

[Agerial]

Hi I am trying to save some processing time by reducing how much the computer needs to sort. Currently I have it set for 90,000 rows to handle the variable amount of information. I was wondering if there was a way to set the sort size based on how many rows actually have values/information in them.</SPAN>
Thanks</SPAN>
Sub WORK7()
'
' WORK7 Macro
'
'
Sheets("WORKSHEET").Select
Columns("A:D").Select
ActiveWorkbook.Worksheets("WORKSHEET").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("WORKSHEET").SORT.SortFields.Add Key:=Range( _
"D1:D90000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("WORKSHEET").SORT
.SetRange Range("A1:D90000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-3])"
Range("A1:C" & Range("G1").Value).Select
Selection.COPY
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste
End Sub

 

[jolivanes]

Please put your code between code tags.

Dim lr As Long
lr = Cells(Rows.Count, 4).End(xlUp).Row

The Range then becomes

Range("D1:D" & lr)

At the beginning of the code use

Application.Screenupdating = False

and at the end use

Application.Screenupdating = True

Have a read here on optimizing and speeding up your code.
Optimize VBA