How to reduce selection
 

I have seen many posts that says not to use "SELECT" and is almost
un-necessary. Following code is mainly based on the selection only.

Sheets("LIST").Range("A:C").Copy Destination:=SHT1.Range("A1")
Set ITRN = Sheets("In").Range("J:J")
Set QTRN = Sheets("In").Range("M:M")
Range("D2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -3).Value)
ActiveCell.Value = Application.WorksheetFunction.SumIf(ITRN,
ActiveCell.Offset(0, -3), QTRN)
ActiveCell.Offset(1, 0).Select
Loop
Set ITRN = Nothing
Set QTRN = Nothing

This code is being repeated 8 times for looping around 4000++ rows
to complete the report generation.

Question:
How can I reduce the "SELECT" part to speed up the macro running?

Any help is appreciated.
Bimal.

How to reduce selection
 

Sheets("LIST").Range("A:C").Copy Destination:=SHT1.Range("A1")
Set ITRN = Sheets("In").Range("J:J")
Set QTRN = Sheets("In").Range("M:M")
set cell = Range("D2")
Do While Not IsEmpty(cell.Offset(0, -3).Value)
cell.Value = Application.WorksheetFunction.SumIf(ITRN, _
Cell.Offset(0, -3), QTRN)
set cell = Cell.Offset(1, 0)
Loop
Set ITRN = Nothing
Set QTRN = Nothing

--
Regards,
Tom Ogilvy


"Bimal" wrote in message
m...
I have seen many posts that says not to use "SELECT" and is almost
un-necessary. Following code is mainly based on the selection only.

Sheets("LIST").Range("A:C").Copy Destination:=SHT1.Range("A1")
Set ITRN = Sheets("In").Range("J:J")
Set QTRN = Sheets("In").Range("M:M")
Range("D2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -3).Value)
ActiveCell.Value = Application.WorksheetFunction.SumIf(ITRN,
ActiveCell.Offset(0, -3), QTRN)
ActiveCell.Offset(1, 0).Select
Loop
Set ITRN = Nothing
Set QTRN = Nothing

This code is being repeated 8 times for looping around 4000++ rows
to complete the report generation.

Question:
How can I reduce the "SELECT" part to speed up the macro running?

Any help is appreciated.
Bimal.

How to reduce selection
 

Hi Bimal,


Try this

Dim i As Long

Application.ScreenUpdating = False
Worksheets("LIST").Range("A:C").Copy Destination:=SHT1.Range("A1")
Set ITRN = Sheets("In").Range("J:J")
Set QTRN = Sheets("In").Range("M:M")
i = 2
Do While Not IsEmpty(Cells(i, "D").Offset(0, -3).Value)
With Cells(i, "D")
.Value = Application.WorksheetFunction.SumIf(ITRN,
..Offset(0, -3), QTRN)
End With
i = i + 1
Loop
Application.ScreenUpdating = True
Set ITRN = Nothing
Set QTRN = Nothing



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bimal" wrote in message
m...
I have seen many posts that says not to use "SELECT" and is almost
un-necessary. Following code is mainly based on the selection only.

Sheets("LIST").Range("A:C").Copy Destination:=SHT1.Range("A1")
Set ITRN = Sheets("In").Range("J:J")
Set QTRN = Sheets("In").Range("M:M")
Range("D2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -3).Value)
ActiveCell.Value = Application.WorksheetFunction.SumIf(ITRN,
ActiveCell.Offset(0, -3), QTRN)
ActiveCell.Offset(1, 0).Select
Loop
Set ITRN = Nothing
Set QTRN = Nothing

This code is being repeated 8 times for looping around 4000++ rows
to complete the report generation.

Question:
How can I reduce the "SELECT" part to speed up the macro running?

Any help is appreciated.
Bimal.