Sorting problem

[textme]

Have a sort macro. sorting 4 columns and a variable number of rows. Rows change weekly. How do i change to only select rows with data in?
this is my current macro which puts me in an endless loop.
Do While Not IsEmpty(ActiveCell)
Range("A2:D40").Select
Range("D2").Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("D6:D40") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:D40")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Loop

Range("A2").Select
End Sub
I am a novice, excuse my ignorance

 

[textme]

I figured it out. I used sort and filter. thanks for listening.

 

[smozgur]

Welcome to the MrExcel Message Board!

If you modified your code to solve this problem, then please paste the final code then it is perfectly fine to mark your own post as the solution as it will help future readers.
Or did you actually use sort and filter Excel functions and gave up using VBA?

In either case, the post that you marked as the solution is not the solution of the original question, that's why I removed the solution marker.