Continue running macro until last line of data.

[Steve]

Using Excel 2003 I am attempting to create a macro where I can copy a set of
numbers, transpose them, sort them and copy/transpose them back to their
original location.

I have the macro working but I do not know how to have the application
continue the function until it has reached the end of my data set. I
currently have the macro running ten lines of data and then it stops.

(Example)
Range("C1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("X1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("X1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("X1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("X1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents

I have this code down to C10 but unfortunately, I have thousands of lines of
data so I need to enter code that keeps the macro running until it has
reached the end of my data set. The total number of lines varies from
hundreds to thousands.

I am sure there is a simple answer but I am having trouble figuring it out.
Any help would be greatly appreciated.

Steve

 

[Tim Williams]

Since Excel 2003 supports sorting column-wise you can skip the copy/paste

'****************************************************
Sub Tester()
Dim rng As Range

Set rng = ActiveSheet.Range("C1")
Do While rng.Value <> ""

With ActiveSheet.Range(rng, rng.End(xlToRight))
.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End With
Set rng = rng.Offset(1, 0)
Loop

End Sub
'****************************************************

Tim

 

[RadarEye]

Hi,

As an alternative for
Do While rng.Value <> ""
you canuse
Do While Not IsEmpty(rng)

hth,
Wouter

 

[Steve]

Tim,

Thanks for the help. It worked perfectly. I knew there had to be an easy
way to get it done.

Steve