Efficient Looping
 

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

--
Message posted from http://www.ExcelForum.com

Efficient Looping
 

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


--
Message posted from http://www.ExcelForum.com

Efficient Looping
 

acw gave you a good fix for the first part.

for the second part you can do

columns(2).SpecialCells(xlblanks).Entirerow.Delete

--
Regards,
Tom Ogilvy


acw wrote in message
...
Hi

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


Tony

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

Range("b1:b10").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
first = True
For Each ce In Selection
If first Then
rastr = ce.Row & ":" & ce.Row
first = False
Else
rastr = rastr & "," & ce.Row & ":" & ce.Row
End If
Next ce
Range(rastr).Select
Selection.Delete Shift:=xlUp
Range("a1").Select

End Sub

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

Hello,

I have a macro that formats a worksheet by looking for null cells in
Column 'A' and filling them with the previous cells data. It then
checks column 'B' for nulls and deletes the row if a null is found.
The
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 in
its full form.

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

Cheers,

.tehwa


---
Message posted from http://www.ExcelForum.com/

Efficient Looping
 

Tom Ogilvy wrote:
acw gave you a good fix for the first part.

for the second part you can do

columns(2).SpecialCells(xlblanks).Entirerow.Delete

--
Regards,
Tom Ogilvy

geebers...that's about as efficient as I will ev4r get it...

Tony, Tom, thank you kindly

--
Message posted from http://www.ExcelForum.com