Sort of a problem?

[Guest]

I have a range that is always changing in size. but I want to be able to sort
it!
I've tried recording a Macro to select the bottom, goto the top etc. but I
get an error "Application definded or Object definded error when running it
from a command button.

Can anyone suggest a peice of code to help me please.
The range always starts at A5 and is 8 columns wide but as I said I don't
know howmany rows as the data changes. I will always be needing to sort bu
Column E Descending.

Thanks
Dean

 

[evgny]

Hi Dean
sub a()
'This count rows in columns A
Dim colA as double
colA = cells(rows.count, "A").end(xlup).row
'this sort the rang
Range("A5:H" & colA).Sort Key1:=Range("E1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

sub end

regards
Yngve

 

[Edward Ulle]

Assuming the last row of data is followed by an empty row the following
macro will work.

Option Explicit

Sub Test()

Dim iRow As Long
Dim strRange As String
Dim rRange As Range

iRow = 4 ' Note offset 0 corresponds to row 1
Do While Not IsEmpty(Range("A1").Offset(iRow, 0))
iRow = iRow + 1
Loop

strRange = "A5:H" + Trim(Str(iRow))
Set rRange = Range(strRange)
rRange.Sort Key1:=Range("E5"), Order1:=xlDescending

End Sub

 

[sutibusan]

I got this to partially work for me. I start with a presorted file. The
active cell is mid file, with 20 blank lines after it, but the macro sorts my
whole file, top to bottom (leaving the blank lines, which is OK).
What I need is a sort of the top set of data & then a sort of the bottom set
of data (around the blank lines).