Excel VBA Subroutine Problem

[rrosenwald]

I have a macro that finds the last row in a spreadsheet and then moves down a column 1 row at a time to check if the current cell is empty. If it is, then it copies the value from the cell immediately above and then continues moving down. I have run this macro on the same worksheet on multiple machines and on some, but not all, I get a spinner culminating with the message shown below. All machines have Windows 10 and either 8 or 16 GB memory and are running pretty much the same basic software (I use them all in various locations and at different times). Can anyone help me figure out what is going on?

Sub FillBlankCells()

Dim LastRow As Long
Dim x As Integer

    Application.ScreenUpdating = False
    Application.CutCopyMode = False
   
ActiveCell.Offset(0, 1).Range("A1").Select

'   Find the last row
    LastRow = Cells(Rows.Count, 4).End(xlUp).Row
   
For x = 1 To LastRow

If IsEmpty(ActiveCell) Then
    ActiveCell.Offset(-1, 0).Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ActiveCell.Offset(1, 0).Select
Else
    ActiveCell.Offset(1, 0).Select
End If

Next x

Application.ScreenUpdating = True

End Sub

 

[mrshl9898]

Try this:

Sub FillBlankCells()

Dim LastRow As Long
Dim x As Integer
Dim y As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ActiveSheet
y = ActiveCell.Column

' Find the last row
LastRow = ws.Cells(ws.Rows.Count, y).End(xlUp).Row

For x = 1 To LastRow

If Cells(x, y) = "" Then
Cells(x, y) = Cells(x - 1, y)
End If

Next x

Application.ScreenUpdating = True

End Sub

 

[rrosenwald]

Unfortunately, same result. I also tried stepping through it and the empty cells didn't get changed.

 

[mrshl9898]

Well that code is pretty light, maybe it's more of an issue with others Excel Applications. This may be helpful

Fix Excel is waiting for another application to complete an OLE action Error

Want to know how to deal with Excel OLE action error then follows the given solutions one by one to fix Excel is waiting for OLE action issue

www.repairmsexcel.com

 

[rrosenwald]

Thank you

 

[rrosenwald]

Thank you so much for the link. It appears that one of the Excel Add-ins, NatSpeak for Excel (added by Dragon Naturally Speaking) was causing the hang. Works like a charm now