Sort data inserts random blank cell at the top??

[sean.dolan93]

Hey all,
This is a pretty basic issue with I'm sure a pretty basic answer, but I'm stuck

At the end of this big complex module which works fine, all I want to do is sort my data (numbers formatted as text) in ascending order.
Every 4th column contains this data, so my code looks like this:

For i = 1 To 37
    Wreport.Select
    Wreport.Range(Cells(5, i), Cells(1000, i)).Select
    Selection.Sort Key1:=Wreport.Cells(5, i), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    i = i + 3
Next i

This works perfectly fine on all the columns EXCEPT the first one...which every time adds one blank cell at the top in cell(5,1) (it does not replace a number but bumps them all down).
I've tried it from the 4th row with Header:xlYes, I've tried deleting some of that stuff in the sort function that I don't know what it does...etc.

Thanks in advance for any help!!

[MarvinP]

Hi Sean,

Instead of using

For i = 1 to 37
..
i = i+3
next i

Try using

For i = 1 to 37 step 3
..
next i

If that doesn't work then you should attach a sample workbook so we can see the space at the top.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[shg]

I suspect the cell that comes to the top is not blank, but contains a null string. Delete the contents and try again.

It's a cardinal sin to modify a loop control variable in the loop. What are you trying to do?

[sean.dolan93]

shg:

I'm not sure that it does because when I manually sort the data including the blank cell it spat out, it puts the blank at the bottom where it should be. Maybe I should have the code just sort column 1 twice.....hmmm.

Also with the i = i + 3 I was just trying to jump from column 1 to 5 to 9...it works fine for that even if I didn't know the standard procedure

[sean.dolan93]

Thanks for your response Marvin,
I tried your version of the code but had the same results. Here is an attached workbook with just the first few sets of data, the first row being the problem. If it's any help, I noticed that as I stepped through the code pasted above, the screen would refresh to show the sorted data in each column immediately after performing the sort function...EXCEPT for the first column. This column would not update to reveal the blank space until the next column was selected in the loop.

[shg]

This worked fine for me for the workbook you posted. Note that if you have related data in the Reason column, it won't sort with the sorted column.

Sub sd()
  Dim iCol          As Long
  Dim wks           As Worksheet

  Set wks = Worksheets("Pre VBA")
  With wks
    For iCol = 1 To 13 Step 4
      .Cells(5, iCol).Resize(1000).Sort Key1:=.Cells(5, iCol), Header:=xlNo
    Next iCol
  End With
End Sub

[sean.dolan93]

shg:
I copied your code into the module, which definitely looks cleaner but still produces the same result. I have stepped through and know that the problem is occurring at this sorting moment and not before/after. I've also verified the formatting of A5 is the same as the cells below...not sure what else to do. I could write in to delete A5 after sorting but I'm afraid it won't do this once input data changes and I would be deleting a value.

Also, do you have any idea why the screen would not update immediately after sorting on only for this column?

[shg]

I ran that exact code on the exact workbook you posted and it ran fine

I think you have some other code in there doing that. Step through the code and watch it run.