ExcelBanter - View Single Post

Victor Delta[_2_]

In article ,
says...

Hi,

Am Sun, 27 Apr 2014 08:55:56 +0200 schrieb Claus Busch:

try:

Sub Randomize()

if you want to fill the exact number of rows like shown in C1 try:

Sub Randomize()
Dim arrOut As Variant
Dim LRow As Long
Dim myRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet
myRows = .Range("C1")
Do
LRow = .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
arrOut = .Range("A1:A" & WorksheetFunction.Min(6, myRows - LRow
+ 1))
.Range("B" & IIf(LRow = 2, 1, LRow)) _
.Resize(rowsize:=UBound(arrOut)) = arrOut
.Calculate
Loop While LRow + 6 < myRows
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Regards
Claus B.

Claus

Many thanks. I'll give these a go and let you know how I get on.

V