Trouble using "Do Until"

[olafsinsight]

This is the goal: I need to get a huge column of numbers into a format to print for a binder. I have a macro that does it one row at a time, but there are 50,000 numbers in the column. I haven't gotten any loop macros to do anything for me yet. I also haven't been able to find an example of a loop that actually does something other than count, but I would love to see one.

This is how I am trying to do it:

Sub Complete ()
Do Until Cells("K1:K11").Value = " "
Range("K1:K10").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("K1:K10").Delete Shift:=xlUp
    ActiveCell.Offset(1, 0).Select
            Loop
End Sub

I'm sure it's just a format issue, but it's making me feel pretty silly.

 

[shg]

I can't see how your code does anything useful. What is it supposed to do?

 

[Audioa84]

This code mimics what you are doing but changes it a bit.

Sub Complete2()
Dim i As Integer
Dim j As Integer
Dim lastRow As Integer
Dim rangex As Integer
Dim remand As Integer


lastRow = ActiveSheet.Cells(1, 11).Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
rangex = Round(lastRow / 10, 0)
remand = lastRow Mod 10
For i = 1 To rangex
   j = j + 1
   Range("K1:K10").Copy
   Range("L" & j).Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
   Range("K1:K10").Delete Shift:=xlUp
Next i
Range("K1:K" & remand).Copy
Range("L" & (j + 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("K1:K" & remand).Delete Shift:=xlUp
End Sub

 

[olafsinsight]

I can't see how your code does anything useful. What is it supposed to do?

It takes the top ten numbers from column K and pastes them onto the active cell sideways, then selects the cell below that active cell. If I can get it to repeat itself, I could let it format one huge column into a printable book.

 

[olafsinsight]

This code mimics what you are doing but changes it a bit.

I don't understand any of that code xD I tried it with a small column and it worked fine, but when I tried the real data it said "Runtime error 6 overflow"

 

[olafsinsight]

I exaggerated the amount of numbers in that column, there are only 43,092. I'm not sure if that makes a difference. I just want my code to rerun until K is blank. Here is the original macro:

Sub RowRun_1()
    Range("K1:K10").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("K1:K10").Delete Shift:=xlUp
    ActiveCell.Offset(1, 0).Select
End Sub

 

[shg]

If olaf's code otherwise works for you, change the word Integer to Long.

 

[Audioa84]

Change the word integer to the word Long. That should fix it. Be forewarned, it will take a few minutes to run.

 

[olafsinsight]

If olaf's code otherwise works for you, change the word Integer to Long.

Thanks, guys, it looks like that did it!
You were both very helpful!