Run time error9, subscript out of range

[rinijg]

I have a macro in which I have to count the number of non empty cells in a row.

I have given the code as follows

Dim re As Long
re = Worksheets("Sheet1").Range("B2", Range("B2").End(xlDown)).Count

But when i run this code, its showing the error "subscript out of range"

Can you tell me how to solve this problem?

 

[JackDanIce]

Try this, although reading what Sektor and Jason are saying I think there's a problem you've not identified or are unable to explain which is why it's causing a problem:

Sub Rinijg()

    Dim n As Long, a As Long, i As Long, j As Long, k As Long, m As Long, l As Long, re As Long
    
    k = 12: m = 12: a = 1
    
    With Worksheets("Sheet1")
        n = .Range("A2).End(xlDown).Row - 1
    End With
    
    With Worksheets("New KK routings")
        re = .Range("B2").End(xlDown).Row - 1
    End With
    
    MsgBox n & " " & re

End Sub

 

[Sektor]

By the way, check worksheets names. Are they spelled correctly?

 

[jasonb75]

jasonb75, Anyway there must be no error even if there's empty row. Then Count will just return 1.

My additional comment was thinking ahead to possible wrong results after the error is eliminated.

Not sure why, you would think it would count 1 if it was empty but for some reason it counts 2.

Just noticed that Worksheets("New KK routings") was changed to Worksheets("NewKKroutings") looks like a good candidate for an error.