CODEWITHSUNDEEP
vbaexcel
user3237076
user3237076

Reputation: 45

VBA macro doesn't go to the end of file excel

I have a macro that puts 0 instead of blank rows there are more than 65000 of them everything works fine but problem is macro stops at line 62000 even if there is data on a next rows. Here is the code:

Sub QuickCull()
    On Error Resume Next
    Columns("a").SpecialCells(xlBlanks).EntireRow.Delete
    Columns("b").SpecialCells(xlBlanks).EntireRow.Delete
    Columns("d").SpecialCells(xlBlanks).EntireRow.Delete

    Dim col As Range
    Set col = Cells(Rows.Count, "E").End(xlUp)

    Dim r As Range
    Set r = Range("E2", col).Resize(, 4)

    Dim cell As Range
    For Each cell In r
        If cell.Value = "" Then
           cell.Value = 0
        Else
           cell.Value = 1
        End If    
    Next cell

    Cells("J1").Activate

End Sub

It seems to me problem is with ranges but not sure at all. What could cause this?

Upvotes: 1

Views: 164

Answers (1)

user4039065
user4039065

Reputation:

The Range.Cells property does not accept the same style of cell address references that a Range object does.

Cells("J1").Activate
'should be,
Range("J1").Activate

If columns E:H are having their values changed to 1 and their blanks changed to 0 then you can continue with the Range.SpecialCells method using xlCellTypeBlanks and xlCellTypeConstants.

Sub QuickCull()
    Dim col As Range, r As Range

    With Worksheets("data")   '<~~ you should know ehat worksheet you are on!
        On Error Resume Next
        .Columns("a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Columns("b").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Columns("d").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

        Set col = .Cells(Rows.Count, "E").End(xlUp)
        Set r = .Range("E2", col).Resize(col.Row - 1, 4)

        r.SpecialCells(xlCellTypeConstants) = 1
        r.SpecialCells(xlCellTypeBlanks) = 0

        .Range("J1").Activate   '<~~ or .Cells(4, "J").Activate
    End With
End Sub

Upvotes: 1

Related Questions