Getting a run time error 6 on Macro that autofills blank values

Question

So I'm using the auto fill macro from here, or the code below: https://www.extendoffice.com/documents/excel/3974-excel-repeat-a-value-until-new-value-is-seen.html

Sub FillDown()
Dim xRng As Range
Dim xRows As Long, xCols As Long
Dim xRow As Integer, xCol As Integer
Set xRng = Selection
xCols = xRng.Columns.CountLarge
xRows = xRng.Rows.CountLarge
For xCol = 1 To xCols
  For xRow = 1 To xRows - 1
    If xRng.Cells(xRow, xCol) <> "" Then
      xRng.Cells(xRow, xCol) = xRng.Cells(xRow, xCol).Value
      If xRng.Cells(xRow + 1, xCol) = "" Then
        xRng.Cells(xRow + 1, xCol) = xRng.Cells(xRow, xCol).Value
      End If
    End If
  Next xRow
Next xCol
End Sub

My data has about 300,000 rows in it. The macro works well on a couple of 1000 rows, but i get a run time error when applying it to larger data sets. Is there any wya this code can be optimized for larger data?

score 0 · Accepted Answer · answered Mar 04 '19 at 15:14

0

Replace:

Dim xRow As Integer, xCol As Integer

with:

Dim xRow As Long, xCol As Long

answered Mar 04 '19 at 15:14

Gary's Student

95,722
10
59
99

Getting a run time error 6 on Macro that autofills blank values

1 Answers1