[compryan]

Hi all,

I am currently working with a dataset where the data in column A is empty until a user enters a rank (i.e. like ranking grades in ascending order). Currently the macro takes the number and moves the row entry to the top by assuming that the rank is to be the highest rank (it then takes the other manual ranks and moves them after this rank in ascending order); however, if a user places a lower rank on an already higher score (for instance #30 on #5), it doesn't move down... it moves up. Simply, it's the logic of the macro. However, I need help in modifying this macro to move any manual ranking to the appropriate position and moving other rows to their appropriate position based on these manual ranks.

For instance, this is what I need it to do:

A: Original Rank 1 Manual Rank 3 Final Rank D
B: Original Rank 2 Empty Final Rank B
C: Original Rank 3 Empty Final Rank A
D: Original Rank 4 Manual Rank 1 Final Rank C
E: Original Rank 5 Empty Final Rank E

Here's the macro thus far:

j = 1
Sheets("POR Ranking").Select
Range("A16").Select
iRow = ActiveCell.CurrentRegion.Rows.Count + 14
For I = 1 To iRow
If ActiveCell.Value <> "" Then
j = j + 1
ActiveCell.Offset(1, 0).Select
Else
iRow = j + 14
sRanking = "Manual Listing"
Go to SetSummary
End If
Next I

Any assistance would be greatly appreciated! Thanks everyone.

[ScottGem]

Are you trying to modify the sheet through a VB front end?

[compryan]

The script was created in the Excel VB editor (behind a command button).

[ScottGem]

Then this belongs in the Spreadsheet forum, not VB. VB is a stand alone programming language. What you need is help with Excel VBA. So I moved the thread for you.