Efficient Looping

  • Thread starter Thread starter tehwa
  • Start date Start date
T

tehwa

Hello,

I have a macro that formats a worksheet by looking for null cells i
Column 'A' and filling them with the previous cells data. It the
checks column 'B' for nulls and deletes the row if a null is found. Th
function I have written is working, but it is very slow:

Dim counter As Integer

Columns(1).Select
ActiveCell.CurrentRegion.Select
areaCount = Selection.Rows.Count

For counter = 1 To areaCount
If Cells(counter, 1).Value = 0 Then
Cells((counter - 1), 1).Copy _
Destination:=Cells(counter, 1)
End If
Next counter

counter = 1

For counter = areaCount To 1 Step -1
If Cells(counter, 2).Value = 0 Then
Rows(counter).Delete
End If
Next counter

This is a dodgy cut 'n' paste job, but it works when the macro is i
its full form.

Can anyone fill me in on how I can do this looping a little mor
efficiently, the other parts of the macro work fine but these ar
adding a lot of time onto the macro's execution.

Cheers,

.tehw
 
H

Try the following. You will have to modify the ranges used

Ton

Sub aaa(
Range("A1:A10").Selec
Selection.SpecialCells(xlCellTypeBlanks).Selec
Selection.Formula = "=r[-1]c
Range("A1:A10").Selec
Application.CutCopyMode = Fals
Selection.Cop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals

Range("b1:b10").Selec
Selection.SpecialCells(xlCellTypeBlanks).Selec
first = Tru
For Each ce In Selectio
If first The
rastr = ce.Row & ":" & ce.Ro
first = Fals
Els
rastr = rastr & "," & ce.Row & ":" & ce.Ro
End I
Next c
Range(rastr).Selec
Selection.Delete Shift:=xlU
Range("a1").Selec

End Su

----- tehwa > wrote: ----

Hello

I have a macro that formats a worksheet by looking for null cells i
Column 'A' and filling them with the previous cells data. It the
checks column 'B' for nulls and deletes the row if a null is found. Th
function I have written is working, but it is very slow

Dim counter As Intege

Columns(1).Selec
ActiveCell.CurrentRegion.Selec
areaCount = Selection.Rows.Coun

For counter = 1 To areaCoun
If Cells(counter, 1).Value = 0 The
Cells((counter - 1), 1).Copy
Destination:=Cells(counter, 1
End I
Next counte

counter =

For counter = areaCount To 1 Step -
If Cells(counter, 2).Value = 0 The
Rows(counter).Delet
End I
Next counte

This is a dodgy cut 'n' paste job, but it works when the macro is i
its full form

Can anyone fill me in on how I can do this looping a little mor
efficiently, the other parts of the macro work fine but these ar
adding a lot of time onto the macro's execution

Cheers

.tehw
 
acw gave you a good fix for the first part.

for the second part you can do

columns(2).SpecialCells(xlblanks).Entirerow.Delete
 
Tom said:
acw gave you a good fix for the first part.

for the second part you can do

columns(2).SpecialCells(xlblanks).Entirerow.Delete
Click to expand...
geebers...that's about as efficient as I will ev4r get it...

Tony, Tom, thank you kindly
 
Back
Top