EXCEL - VBA recorded macro giving runtime error “1004”

Question

I made a short VBA code which basically just AutoFits text/content inside cells. I recorded this macro and then remade it so that the code would run through the whole sheet:

Sub Macro3()
    Dim lastRowIndex As Integer
    Dim rowIndex As Integer
    lastRowIndex = 2600

For rowIndex = 0 To lastRowIndex
    If ActiveSheet.Cells(rowIndex, 1).Value <> "" Then
        If ActiveSheet.Rows(rowIndex).RowHeight < 10.7 Then
            If ActiveSheet.Rows(rowIndex).RowHeight > 8 Then
                ActiveSheet.Rows(rowIndex).Select
                With Selection
                    .HorizontalAlignment = xlGeneral
                    .VerticalAlignment = xlBottom
                    .WrapText = True
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
                Selection.Rows.AutoFit
            End If
        End If
    End If
Next rowIndex


End Sub

The application stops at the IF conditions. They're there because I don't want to affect all the cells, just the ones I need to modify.

When I try to run this code it gives me a this Runtime Error "1004" - Aplication-defined or Object-defined error. I have no idea why...

I tried putting Option Explicit above the code since I read somewhere it then gives you a more detailed information about the error but that doesn't seem to work either. I've never really used VBA before so I have no clue what's the reason for the error.

(Also why is a part of the code above shifting to the left? I cant fix it)

Answer 1

CELLS() indexing starts at 1 so:

For rowIndex = 1 To lastRowIndex

(There may be other errors.)

Answer 2

1. Use Option Explicit at the top of your code
2. Give your sub a meaningful name
3. As mentioned, loop from 1
4. Use an explicit worksheet reference (not activesheet), and hold that in a With statement then use the dot operator within
5. Use vbNullString rather than empty string literal "" for faster comparisons
6. Combine If conditions into one line to reduce nesting and enhance readability
7. Use With statement to hold reference to current row
8. Use Long not Integer as working with rows and in future this could risk overflow with larger numbers
9. Currently lastRowIndex is a constant so declare as such

Code:

Option Explicit
Public Sub FormatRows()
    Const lastRowIndex As Long = 2600
    Dim rowIndex As Long

    With ThisWorkbook.Worksheets("Sheet1")
        For rowIndex = 1 To lastRowIndex
            If Not .Cells(rowIndex, 1).Value <> vbNullString Then
                If .Rows(rowIndex).RowHeight > 8 And .Rows(rowIndex).RowHeight < 10.7 Then
                    With .Rows(rowIndex)
                        .HorizontalAlignment = xlGeneral
                        .VerticalAlignment = xlBottom
                        .WrapText = True
                        .Orientation = 0
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                        .MergeCells = False
                        .AutoFit
                    End With
                End If
            End If
        Next
    End With
End Sub