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?

 

[rinijg]

a small mistake in the above post. I have to count the number of nonempty cells in a column, not row

 

[Sektor]

If Sheet1 is not active sheet, then you'll get incorrect range:

Dim re As Long
re = Worksheets("Sheet1").Range("B2", [B][COLOR="Red"]Range("B2").End(xlDown)[/COLOR][/B]).Count

The range in red is referring to active sheet. If active sheet is, say, Sheet2 then red code belongs to Sheet2.
Here's correction:

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

 

[rinijg]

I changed the code as follows.

Dim n As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim l As Integer
Dim re As Long
Worksheets("Sheet1").Activate
k = 12
m = 12
a = 1
n = Worksheets("Sheet1").Range("A2", Range("A2").End(xlDown)).Count
Worksheets("New KK routings").Activate
With Worksheets("New KK routings")
    re = .Range("B2", .Range("B2").End(xlDown)).Count
End With

Still it is showing the same error. The value of re should be 7308

 

[Sektor]

There's no need to activate sheet. Read carefully my previous post.

With Worksheets("Sheet1")
    n = .Range("A2", .Range("A2").End(xlDown)).Count
End With
With Worksheets("New KK routings")
    re = .Range("B2", .Range("B2").End(xlDown)).Count
End With

 

[rinijg]

This is my code now

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

Still it is showing the same error. It is not showing error for

With Worksheets("Sheet1")
    n = .Range("A2", .Range("A2").End(xlDown)).Count
End With

It is showing error for only

With Worksheets("Sheet1")
    n = .Range("A2", .Range("A2").End(xlDown)).Count
End With

 

[jasonb75]

There's no need to activate sheet. Read carefully my previous post.

With Worksheets("Sheet1")
    n = .Range("A2", .Range("A2").End(xlDown)).Count
End With
With Worksheets("New KK routings")
    re = .Range("B2", .Range("B2").End(xlDown)).Count
End With

Where you are overlooking simple fact, the sheet is being activated, so your theory that the code was looking at ranges on different sheets is incorrect, meaning your previous post is still wrong.

Maybe

End(xlUp)

Alternatively try

re = WorksheetFunction.CountA(Columns("A"))-1

Note:- only use "-1" at the end of the code line if A1 is non-blank, likewise for the column B equivilant.

 

[Sektor]

Try this:

Dim n As [B][COLOR="Red"]Long[/COLOR][/B]

 

[Sektor]

jasonb75, Prove it.

Worksheets("Sheet1").Range("A2", [B][COLOR="Red"]Range("A2").End(xlDown)[/COLOR][/B])

If Sheet1 IS NOT ACTIVE, red refers to active sheet, which, of cource, is NOT Sheet1.

 

[rinijg]

I am really sorry.. Actually that was a mistake in typing. Its not showing error for calculating the value of n. Its showing error in calculating the value of re. That is first With.. end with is executed properly. Second with..end with is showing this error