ExcelBanter - Scrolling the rows up, while updating.

this is my code which lists 1000 rows like a snap, can you help to show while
writing

Sub PODFollowup()
Dim sh1 As Worksheet, sh As Worksheet
Dim Loc_b3, Veh_c3, dtStart As Date, dtend As Date, ValDate As Date
Dim cell As Range, rng As Range, rw As Long, cmt1 As String, cmt2 As String,
cmt3 As String
Dim BAN_Number As String, Filein As String
Filein = Worksheets("mysheet").Range("e7").Value

Set sh1 = Worksheets("NOpod")
Set sh = Worksheets(Filein)
Loc_b3 = sh1.Range("B1")
cmt1 = Worksheets("Mysheet").Range("e3")
cmt2 = Worksheets("Mysheet").Range("e4")
cmt3 = Worksheets("Mysheet").Range("e5")
ValDate = sh1.Range("G1")

sh1.Range("a7:n5000").Clear ' ClearContents
rw = 7

Application.ScreenUpdating = False

Set rng = sh.Range(sh.Cells(3, 1), sh.Cells(Rows.Count, 1).End(xlUp))

For Each cell In rng
If cell.Offset(0, 49) 0 And cell.Offset(0, 49) < "" Then
sh1.Cells(rw, 2) = cell.Offset(0, 1) ' branch
sh1.Cells(rw, 3) = cell.Offset(0, 5) ' godown number
sh1.Cells(rw, 4) = cell.Offset(0, 17) ' bay number
sh1.Cells(rw, 5) = cell.Offset(0, 9) ' invoice number
sh1.Cells(rw, 6) = cell.Offset(0, 10) ' invoice Date
sh1.Cells(rw, 6).NumberFormat = "dd-mmm-yy"
sh1.Cells(rw, 7) = cell.Offset(0, 22) ' vendor item code
sh1.Cells(rw, 8) = cell.Offset(0, 12) ' destination
sh1.Cells(rw, 9) = cell.Offset(0, 14) ' Veh No
sh1.Cells(rw, 10) = cell.Offset(0, 49) ' our item code
sh1.Cells(rw, 11) = cell.Offset(0, 43) ' POD Receipt date
sh1.Cells(rw, 11).NumberFormat = "dd-mmm-yy"
sh1.Cells(rw, 11).HorizontalAlignment = xlCenter
sh1.Cells(rw, 12) = cell.Offset(0, 42) ' REmarks
' BAN_Number = cell.Offset(0, 18) ' ban number as comment
' sh1.Cells(rw, 8).AddComment Text:=BAN_Number
rw = rw + 1
End If

Next cell ' cell range (next sheet)
'ActiveCell.Select

' Little formatting
sh1.Range(sh1.Cells(rw, 2), sh1.Cells(rw, 14)).Select
Selection.Interior.ColorIndex = 34
Selection.Font.ColorIndex = 25
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

rw = rw + 1
' End If

Frsum = 0
costsum = 0

'Next sh

Range("e1").Select
Application.ScreenUpdating = True

End Sub

"p45cal" wrote:

Eddy Stan;471254 Wrote:
with computers of now a days, scrolling may be useless, but just a wish
i am listing a selective data(1000 rows) from mass data 40000 rows,
inventory sheet.
the listing takes hardly 30-45 secs maximum
does VB write row by row and cell by cell or it writes like snap in one
shot
of all 1000 rows, keeping everything in memory

if it writes, i wish it can scroll while writing in the excel sheet.
any code to do that scrolling while writing.

It can do both. If it writes a row at a time then it's easy to scroll
at the same time in code. What's your code look like?

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=130226