PDA

View Full Version : Help to use an array for this little section of code



bensdb
02-28-2014, 09:44 AM
Hi.
I have a large GUI with loads of macro's. This small section of code is running very slow (about 2 seconds sometimes- which is quite considerable for such a small bit of code), and i would like to be able to make it go faster.
I think it is because it is deleting rows of data in a sheet, in a loop.

I was thinking that if I passed the data to an array to start, and then did the same processes to it that it would probably be faster. Obviousley I would have to redim the array in the loop. What do you think...would it be faster or am I wasting my time?

What I really want is help making this work as an array. I wrote this chunk of code at 2am one night after hours on it, and cannot get my head round the logic.



x = 0
For i = 0 To sdlr
poo = 0
z = 5

Do While ws2.Cells(i + 2 + x, z) = "" And z <= weeks + 4
poo = poo + 1
z = z + 1
Loop

If poo = weeks Then
ws2.Rows(i + 2 + x).Delete
x = x - 1
End If
Next


It basically loops through my range, and where all data in a row is blank apart from the first 3 columns, it deletes that row. The variable 'weeks' just corresponds to the number of columns of data there are in the range, that need to be looped through looking for blanks.

Please help a novice, vba guru's, or advise if you think the redim array method might be just as slow!!!

Bob Phillips
02-28-2014, 09:50 AM
You can speed it by turning calculations and screenupdating off


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

x = 0
For i = 0 To sdlr
poo = 0
z = 5

Do While ws2.Cells(i + 2 + x, z) = "" And z <= weeks + 4
poo = poo + 1
z = z + 1
Loop

If poo = weeks Then
ws2.Rows(i + 2 + x).Delete
x = x - 1
End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

snb
03-02-2014, 09:46 AM
sub M_snb()
sn=cells(1).currentregion

for j=2 to ubound(sn)
if rows(j).specialcells(2).count <4 then sn(j,1)=""
next

cells(1).currentregion=sn
columns(1).specialcells(4).entirerow.delete
End Sub