Excel VBA Subroutine Problem

rrosenwald

New Member
Joined
Jul 7, 2012
Messages
7
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?

1596146480063.png




VBA Code:
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
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this:

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,241
Messages
6,177,370
Members
452,773
Latest member
D P

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
Back
Top