Durchnummerierung der Spalte A

[santana]

Einen schönen guten Abend,

ich möchte in Spalte A jeder Zelle in Spalte B eine fortlaufende Nummer zuordnen. Dazu habe ich mir ein kleines funktionsfähiges Makro geschrieben.

Code:

Sub Nummerierung_Spalte_A()
Dim i as Integer
ReDim Num(1 To Cells(Rows.Count, 2).End(xlUp).Row)
Columns(1).ClearContents

For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
Num(i) = i
Next i

Range("A1:A" & Cells(Rows.Count, 2).End(xlUp).Row) = Application.Transpose(Num)

End Sub

Auch wenn der Code mit Feldern läuft, brauche ich eine Schleife und Schleifen sind ja langsam. Gibt es eine (viel) schnellere Methode?
Gibt es eine (viel) einfachere Methode?

Danke für eure Hilfe.

[RPP63]

Hi!

Gibt es eine (viel) schnellere Methode?

Schleifen sind zwar langsam, aber es kommt auf die Art an.
Deine Schleife läuft in einem Array, also komplett im Arbeitsspeicher.
Außerdem schreibst Du den Arrayinhalt en bloc ins Tabellenblatt, dies ist die schnellste Methode.
Was mir jedoch nicht gefällt ist die fehlende Referenzierung auf die richtige Tabelle.
Bei Dir muss sie aktiv sein.

Gibt es eine (viel) einfachere Methode?

Aber ja!  :19:
Schreibe die Formel =ZEILE() in den Bereich der Spalte A und tausche die Formel gegen den Wert.
Als Makro:
Modul Modul1

Option Explicit 
 
Sub RPP() 
With Tabelle1 
   With .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp).Offset(0, -1)) 
      .Formula = "=ROW()" 
      .Value = .Value 
   End With 
End With 
End Sub 

Gruß Ralf

[ratrad]

Laufzeit mit Formel wirklich schneller als Feldvariable? - Bei mir bei weitem nicht. Was sagt ihr Leute?

Code:

Option Explicit
Public Declare PtrSafe Function GetTickCount Lib "kernel32.dll" () As Long

Sub Feld_laufzeit()
Dim i As Long
Dim lngStartTime As Long, lngStopTime As Long
Columns(1).ClearContents
ReDim Num(1 To Cells(Rows.Count, 1).Row)

lngStartTime = GetTickCount
For i = 1 To Cells(Rows.Count, 1).Row
Num(i) = i
Next i

Range("A1:A" & Cells(Rows.Count, 1).Row) = Application.Transpose(Num)

lngStopTime = GetTickCount
Debug.Print "Laufzeit mit Feldvariable = " & (lngStopTime - lngStartTime) / 1000 & " Sekunden."

End Sub

'#############

Sub RPP_Laufzeit_Version()
Dim lngStartTime As Long, lngStopTime As Long
lngStartTime = GetTickCount

With Tabelle1
  With .Range(.Cells(1, 1), .Cells(.Rows.Count, 2))
     .Formula = "=ROW()"
     .Value = .Value
  End With
End With

lngStopTime = GetTickCount
Debug.Print "Laufzeit mit Formel = " & (lngStopTime - lngStartTime) / 1000 & " Sekunden."

End Sub

(Habe mal bei beiden Varianten den Code leicht modifiziert, so dass er alle Zellen in Spalte A durchläuft.)

[RPP63]

Moin ratrad!
Wo habe ich oben geschrieben, dass es die schnellere Variante ist? 
Ich schrieb über die "(viel) einfachere Variante".
Die "Bremse" in meinem Makro ist .Value = .Value
Dies sollte man bei extrem großen Datenmengen austauschen durch:
.Copy: .PasteSpecial xlPasteValues

Dann klappt es auch mit der Geschwindigkeit.
Meine Formellösung ist dann mehr als doppelt so schnell wie die Array-Variante (ausgehend von Deiner gesamten Spalte A):

Code:

Laufzeit mit Formel = 3,078 Sekunden.
Laufzeit mit Feldvariable = 7,735 Sekunden.

Übrigens läuft bei mir (Excel2010) .Transpose() bei dieser Datenmenge in einen Fehler (ich habe da Limits im Hinterkopf, ich meine bei 2^16 ist Schluß).
Erst nachdem ich ein 2D-Array draus gemacht habe und auf .Transpose verzichtete, bekam ich das Makro überhaupt zum Laufen:
( Ich habe mal aus Range("A1:A" & Cells(Rows.Count, 1).Row) Range("A1:A" & Rows.Count) gemacht)

Code:

ReDim Num(1 To Rows.Count, 1 To 1)
For i = 1 To Cells(Rows.Count, 1).Row
  Num(i, 1) = i
Next i
Range("A1:A" & Rows.Count) = Num

Ich freue mich auf Deine qualifizierte Antwort. :19:

Gruß Ralf

[chris-ka]

Hi,

Code:

Sub fill_me()
With Range("A1")
    .Value = 1
    .AutoFill Destination:=Range("A1:A" & Cells(Rows.Count, 2).End(xlUp).Row), Type:=xlFillSeries
End With
End Sub

wie sieht es da mit Geschw. aus.?
bei 100 000 dürfte es normalerweise keine 0,1 Sek. brauchen

lg
Chris

[RPP63]

Moin Chris!
Für 2^20 Zeilen 1,0 Sekunden!
Sicherlich unschlagbar schnell! 

Gruß Ralf

[chris-ka]

Hi,

so hat mich jetzt auch Interessiert. :)

1.  Short 0,36013 Long 0,36013 Average 0,36013
2.  Short 0,60323 Long 0,60323 Average 0,60323
3.  Short 0,60461 Long 0,60461 Average 0,60461
4.  Short 0,59849 Long 0,59849 Average 0,59849
5.  Short 0,61059 Long 0,61059 Average 0,61059

Bei 1.000.000 Zellen erster Durchlauf 0,36 sec.

Wenn die Zellen in A befüllt sind dann 0,6 Sek.
System:    

Datei:

  fill.xlsm (Größe: 25,23 KB / Downloads: 3)

Sub Main ausführen
Im Direktfenster werden die Zeiten angezeigt.

lg
Chris

[RPP63]

Schnief! :22:
Will auch einen i5 …
Erstaunlich:
Mein Celeron wird schneller ab dem zweiten Durchlauf.

Code:

Running 5 tests with 1 timings for 1 calls of 'fill_me'
Starttime 18.02.2016 09:39:19
1.  Short 1,00285 Long 1,00285 Average 1,00285
2.  Short 0,93587 Long 0,93587 Average 0,93587
3.  Short 0,93304 Long 0,93304 Average 0,93304
4.  Short 0,93060 Long 0,93060 Average 0,93060
5.  Short 0,93978 Long 0,93978 Average 0,93978
Result:
0.  Short 0,93060 Long 1,00285 Average 0,94843
Endtime 18.02.2016 09:39:24

Gruß Ralf

[ratrad]

Hallo Leute, lieber Ralf,

schöne Diskussion. Das wollte ich.  Leider kam mir chris-ka Codemäßig jetzt etwas zuvor, wollte gestern abend noch den kürzesten Code posten, war dann aber zu faul den Rechner nochmal hochzufahren.
Sei's drum. Hier mein 2 Zeiler, wenn in Spalte B etwas steht:

Code:

Sub Auffüllen()
Range("A1:A2") = Application.Transpose(Array("1", "2"))
Range("A1:A2").AutoFill Destination:=Range("A1:A" & Cells(Rows.Count, 2).End(xlUp).Row)
End Sub

Nur für Spalte A, habe damit die kürzesten Laufzeiten erreicht.

Code:

Sub Methode_autofill_Laufzeit()
Columns(1).ClearContents
Dim lngStartTime As Long, lngStopTime As Long
lngStartTime = GetTickCount
Range("A1:A2") = Application.Transpose(Array("1", "2"))
Range("A1:A2").AutoFill Destination:=Columns("A:A")
lngStopTime = GetTickCount
Debug.Print "Laufzeit mit Autofill = " & (lngStopTime - lngStartTime) / 1000 & " Sekunden."
End Sub

Bis dann.

[ratrad]

Hallo Ralf,

habe mal wieder diagonal gelesen und sehe deine Verbesserungen erst jetzt. Werde die auf jeden Fall nochmal ausprobieren
und dir dann auch antworten. Muß aber leider jetzt weg.

So long.