Wow, I just tried the same file agian with your additions and it run in a
about 10 seconds!

Thanks A LOT!
--
I am not where I intended to go, but I think I am where I am supposed to be!


"John Bundy" wrote:

There are more elegant ways for sure, but yours is ok. Adding these 2 lines
to the beginning and end should sppe it up substantially. It holds off
displaying the changes and all calculations until you are done, then starts
it again.

Sub FillAllData()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

FillCount = 3
For counter = 5 To nRow
CellValue = Sheets(BSheets).Range("O" & counter).Value
If CellValue 0 Then
Sheets(AllSheet).Range("A" & FillCount).Value =
Sheets(BSheets).Range("O" & counter).Value
Sheets(AllSheet).Range("B" & FillCount).Value =
Sheets(BSheets).Range("P" & counter).Value
Sheets(AllSheet).Range("D" & FillCount).Value =
Sheets(BSheets).Range("Q" & counter).Value
Sheets(AllSheet).Range("E" & FillCount).Value =
Sheets(BSheets).Range("R" & counter).Value
Sheets(AllSheet).Range("F" & FillCount).Value =
Sheets(BSheets).Range("S" & counter).Value
Sheets(AllSheet).Range("G" & FillCount) = Sheets(BSheets).Range("T" &
counter).Value
FillCount = FillCount + 1
End If
Next counter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"thewizz" wrote:

I have the following code, which is looking to see if there is data in column
"O" starting with row 5 and ending with the "nRow" which is the last row with
data in the column. "nRow" could be as high as the maximum rows in Excel. If
there is data greater than "0" it copies varius cells to other cells in
another sheet.

My question is: Is there a more efficiant way to do this? It takes a long
time to run this code when there is a lot of data in "O".

Thank you!


Sub FillAllData()

FillCount = 3
For counter = 5 To nRow
CellValue = Sheets(BSheets).Range("O" & counter).Value
If CellValue 0 Then
Sheets(AllSheet).Range("A" & FillCount).Value =
Sheets(BSheets).Range("O" & counter).Value
Sheets(AllSheet).Range("B" & FillCount).Value =
Sheets(BSheets).Range("P" & counter).Value
Sheets(AllSheet).Range("D" & FillCount).Value =
Sheets(BSheets).Range("Q" & counter).Value
Sheets(AllSheet).Range("E" & FillCount).Value =
Sheets(BSheets).Range("R" & counter).Value
Sheets(AllSheet).Range("F" & FillCount).Value =
Sheets(BSheets).Range("S" & counter).Value
Sheets(AllSheet).Range("G" & FillCount) = Sheets(BSheets).Range("T" &
counter).Value
FillCount = FillCount + 1
End If
Next counter

End Sub
--
I am not where I intended to go, but I think I am where I am supposed to be!