Trouble using "Do Until"

olafsinsight

New Member
Joined
Jan 27, 2014
Messages
5
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:

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
I can't see how your code does anything useful. What is it supposed to do?
 

Audioa84

Board Regular
Joined
Oct 28, 2013
Messages
61
This code mimics what you are doing but changes it a bit.
Code:
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

New Member
Joined
Jan 27, 2014
Messages
5
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

New Member
Joined
Jan 27, 2014
Messages
5
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

New Member
Joined
Jan 27, 2014
Messages
5
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:

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
If olaf's code otherwise works for you, change the word Integer to Long.
 

Audioa84

Board Regular
Joined
Oct 28, 2013
Messages
61
Change the word integer to the word Long. That should fix it. Be forewarned, it will take a few minutes to run.
 

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top