CODEWITHSUNDEEP
excelvba
Worst SQL Noob
Worst SQL Noob

Reputation: 189

Method 'Range' of object'_Global' Failed in VBA

I am new to VBA, and I have a code which I want to count how many no empty rows from A2 to the end of the sheet, but I am keeping get this error, and the code is following:

I cannot finger out what the problem is

Thanks Pudge

Upvotes: 0

Views: 314

Answers (2)

user3598756
user3598756

Reputation: 29421

not clear what OP actually needs ("not blank" cells or "not blank" cells resulting from formulas, or what else)

so I throw in just some examples:

Option Explicit

Sub TicketCopy()

    With Worksheets("Formulas")
        With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)) '<--| reference column "A" cells from row 2 to last not empty one

            ' all cells in range with formulas only
            With .SpecialCells(xlCellTypeFormulas)
                MsgBox "The Rows Count= " & .Count ' cells number
                MsgBox "The Rows Count= " & WorksheetFunction.Count(.Cells) ' cells resulting "not blank" (a 'zero' is a "not blank")
                MsgBox "The Rows Count= " & WorksheetFunction.CountIf(.Cells, "") ' cells resulting "blank" (a 'zero' is a "not blank")
                MsgBox "The Rows Count= " & WorksheetFunction.CountBlank(.Cells) '  cells resulting "blank" (a 'zero' is a "not blank")
            End With

            'all cells in range
            MsgBox "The Rows Count= " & .Count ' cells number
            MsgBox "The Rows Count= " & WorksheetFunction.Count(.Cells) 'cells resulting "not blank" (a 'zero' is a "not blank")
            MsgBox "The Rows Count= " & WorksheetFunction.CountIf(.Cells, "") 'cells resulting "blank" (a 'zero' is a "not blank")
            MsgBox "The Rows Count= " & WorksheetFunction.CountBlank(.Cells) ' cells resulting "blank" (a 'zero' is a "not blank")
        End With
    End With

End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Simple fix to the Set:

Sub TicketCopy()
    With Worksheets("Formulas")
        Dim K As Range
        Dim M As Long
        Set K = Range(.Range("A2"), .Range("A2").End(xlDown))
        M = K.Rows.Count
        MsgBox "The Rows Count=" & M
    End With
End Sub

enter image description here

Note this is one less than the bottom of the column.

Upvotes: 1

Related Questions