PDA

View Full Version : [SOLVED] If Column A ıs Empty and if Column I has a data copy them to Column A ..



parscon
09-06-2018, 12:05 AM
Hi , I am using this VBA that check If column A is empty and if column I has data will copy them to column A and my code working very well just the problem is speed . i have about 830000 Row and it take so much time
is it possible to help me to make it faster :(



Dim r As Range
For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If IsEmpty(r) Then
r.Offset(0, 8).Copy r.Offset(0, 0)
End If
Next


Thanks for your time and help

werafa
09-18-2018, 02:35 AM
have you disabled:
->worksheet calculation
-> Events
-> Screen updating

?

this can make a fairly big difference

Werafa

parscon
09-18-2018, 04:44 AM
Thank you so much really useful tips...

Paul_Hossler
09-18-2018, 11:17 AM
Hi , I am using this VBA that check If column A is empty and if column I has data will copy them to column A and my code working very well just the problem is speed . i have about 830000 Row and it take so much time
is it possible to help me to make it faster :(

Thanks for your time and help

1. I think you're doing a lot of unnecessary looping

2. I don't think you need to test the value in column B since even if A and I are both blank, moving a blank to a blank does the same thing as testing




Option Explicit

Sub test()
Dim r As Range, c As Range

With ActiveSheet
On Error Resume Next
Set r = Intersect(.Columns(1).SpecialCells(xlCellTypeBlanks), .UsedRange)
For Each c In r.Cells
c.Value = c.Offset(0, 8).Value
Next
On Error GoTo 0
End With
End Sub