Flickering Screen while running VBA

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi,

My screen flickers while running the code. I have added the ScreenUpdating into my code however it still flickers.

I have attached my code below

VBA Code:
Sub Specific_Column()
'Indirect Copy & Paste
'Copy from SOC Filter List 1 to Packing List
Application.ScreenUpdating = False
Dim lastrow As Long, erow As Long
Dim i As Integer
On Error Resume Next
Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'find last filled row'
lastrow = Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Cells(Rows.Count, 1).End(xlUp).Row

erow = Workbooks("Packing List.xlsm").Worksheets("Packing List").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 4 To lastrow

    'SCN No.
    Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Cells(i, 2).Copy
    Workbooks("Packing List.xlsm").Worksheets("Packing List").Cells(erow, 1).PasteSpecial Paste:=xlPasteValues
    'Name
    Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Cells(i, 3).Copy
    Workbooks("Packing List.xlsm").Worksheets("Packing List").Cells(erow, 2).PasteSpecial Paste:=xlPasteValues
    'Delivery date
    Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Cells(i, 6).Copy
    Workbooks("Packing List.xlsm").Worksheets("Packing List").Cells(erow, 3).PasteSpecial Paste:=xlPasteValues
    'Order No.
    Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List").Cells(i, 11).Copy
    Workbooks("Packing List.xlsm").Worksheets("Packing List").Cells(erow, 4).PasteSpecial Paste:=xlPasteValues
    
    erow = erow + 1
Next i
Application.ScreenUpdating = True
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jxj2()
   Dim lastrow As Long
   Dim Ary As Variant

   With Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List")
      On Error Resume Next
      .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      On Error GoTo 0
      lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
      Ary = Application.Index(.Range("A4:K" & lastrow).Value, Evaluate("row(1:" & lastrow - 3 & ")"), Array(2, 3, 6, 11))
   End With
   With Workbooks("Packing List.xlsm").Worksheets("Packing List")
      .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ary), 4).Value = Ary
   End With
End Sub
 

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jxj2()
   Dim lastrow As Long
   Dim Ary As Variant

   With Workbooks("SOC Filter List 1.xlsm").Worksheets("Filter List")
      On Error Resume Next
      .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      On Error GoTo 0
      lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
      Ary = Application.Index(.Range("A4:K" & lastrow).Value, Evaluate("row(1:" & lastrow - 3 & ")"), Array(2, 3, 6, 11))
   End With
   With Workbooks("Packing List.xlsm").Worksheets("Packing List")
      .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ary), 4).Value = Ary
   End With
End Sub

Hi, the code worked and the screen no longer flickers. Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,171
Messages
5,640,579
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top