How to describe Active Cell in a " Do - Loop" macro

Hi,

If you have a column in the report that can be sorted, maybe create a macro that will sort out the blank lines.

Something like this:

Sub Sort_to_remove_blanks()

' Set this range for the top left and bottom most right corner of the report
' Use a row number that you know will be far enough down
Range("A2:J1000").Select
 ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A1000"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Sheet2").Sort
    ' Set this range to the same as above so that the sort is done over all the rows
    .SetRange Range("A2:J1000")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
 End With

Range("A2").Select

End Sub

The thing in this is making sure you select far enough down on the bottom right range cell so that you are sure to cover whatever length the report may be.

I tried to do this dynamically with the xlDown to try and grab all the cells .. but any space or blank line is where it sets the range. So I opted for this as an example.

Hope this helps.

If anyone has another way to dynamically grab the range .. I would love to see it.