#### Jak

##### Well-known Member

- Joined
- Apr 5, 2002

- Messages
- 825

Application.ScreenUpdating = False

it disables the flicker and is therefore desirable. What I have noticed however is that the egg timer/arrow cursor flashes instead until the macro finishes. Is there a line of code to stop the flashing?

heres a macro that I use which demonstrates the point in question.

Sub Macro1()

'

' If A1 is blank then pop up Msg message

If Range("A1").Value = "" Then

MsgBox "What are you doing?" & Chr$(13) & _

"There is No data to sort out!"

Exit Sub

End If

Application.ScreenUpdating = False

' No need to set default values of TextToColumns input parameters

Columns(1).TextToColumns _

Destination:=Range("A1"), _

Comma:=True, _

FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _

Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _

Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _

Array(12, 1), Array(13, 1))

Rows(1).Insert Shift:=xlDown

Columns(1).Insert Shift:=xlToRight

Columns(1).Insert Shift:=xlToRight

Columns(1).Insert Shift:=xlToRight

Range("A1") = "Processor"

Range("A2") = "JJ"

Range("B1") = "Scheme No"

Range("C1") = "batch No"

Range("D1") = "First Name"

Range("E1") = "Initials"

Range("F1") = "Surname"

Range("G1") = "Previous Candidate"

Range("H1") = "Previous Candidate No"

Range("I1") = "Date of Birth"

Columns(10).Insert Shift:=xlToRight

Range("J1") = "Year Of Birth"

Range("J2") = Right(Range("I2"), 2)

Range("K1") = "Ethnic Origin"

Range("L1") = "Gender"

Dim c As Range

Set c = Range("D2")

While Not IsEmpty(c.Value)

c.Offset(0, -3).Value = "JJ"

Set c = c.Offset(1, 0)

Wend

Set c = Nothing

Dim n As Long

For n = 2 To Cells(500, 9).End(xlUp).Row

Cells(n, 10).Value = Right(Cells(n, 9).Value, 2)

Next n

LastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row

ColumnNumber = 7

Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber)).Replace _

What:="", Replacement:="N"

ColumnNumber = 11

Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber)).Replace _

What:="", Replacement:="J"

Range("B2").Select

schemenumber = InputBox("Enter the Scheme Number:")

batchnumber = InputBox("Enter the Batch Number:")

Dim z As Range

Set z = Range("A2")

While Not IsEmpty(z.Value)

z.Offset(0, 1).Value = schemenumber

z.Offset(0, 2).Value = batchnumber

Set z = z.Offset(1, 0)

Wend

Set z = Nothing

Dim r As Range

For Each r In Range("A2:G500,K2:L500")

r.Value = UCase(r.Value)

Next

i = 0

Do While i< 500

i = i + 1

Cells(i, 7).Select

CellValue = ActiveCell.Value

If IsNumeric(CellValue) = True And Not CellValue = "" Then

CellValue = CStr(CellValue)

TheLenght = Len(CellValue)

Do While TheLenght< 6

CellValue = "0" + CellValue

TheLenght = Len(CellValue)

Loop

ActiveCell.Value = "'" + CellValue

End If

Loop

Columns(6).Select

Selection.Replace What:="MC", Replacement:="Mc", LookAt:=xlPart, _

SearchOrder:=xlByColumns, MatchCase:=True

Columns(2).NumberFormat = "@"

Columns("M:X").dELETE Shift:=xlToLeft

With Range("A:L")

.ClearFormats

.Font.Name = "Arial"

.Font.Size = 10

.HorizontalAlignment = xlLeft

Columns(2).EntireColumn.AutoFit

Columns(3).EntireColumn.AutoFit

Columns(4).EntireColumn.AutoFit

Columns(6).EntireColumn.AutoFit

Columns(9).EntireColumn.AutoFit

Columns(10).EntireColumn.AutoFit

Columns(12).EntireColumn.AutoFit

Columns("J:J").Select

Selection.NumberFormat = "00"

Range("A1").Select

Application.ScreenUpdating = True

End With

End Sub

This message was edited by Jak on 2002-10-20 10:18