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]

I have put my code like this now

Worksheets("Sheet1").Activate
n = Worksheets("Sheet1").Range("A2", Range("A2").End(xlDown)).Count
Worksheets("NewKKroutings").Activate
 re = Application.WorksheetFunction.CountA(Columns("A")) - 1

I declared n as integer and re as long. It is showing this "subscript out of range" error while running

Worksheets("NewKKroutings").Activate

 

[jasonb75]

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.

Read the code in post #4,

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

Sheet activated and still failed!

 

[Sektor]

You activate sheet just for not to write Worksheets("Sheet1")? Is it cool?

 

[rinijg]

Could you please tell me what change should I make to eliminate the error? I tried all possible ways, but seems like nothing is working out..

 

[Sektor]

You didn't declare "a" variable. If you have Option Explicit at the top of module, then code won't work.

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", .Range("A2").End(xlDown)).Count
    End With
    
    With Worksheets("New KK routings")
        re = .Range("B2", .Range("B2").End(xlDown)).Count
    End With
    
    MsgBox n & " " & re

End Sub

 

[rinijg]

I just copy pasted the same code as above and tried to run RiniJG macro It is still showing the same error at the same line

 

[jasonb75]

I just copy pasted the same code as above and tried to run RiniJG macro It is still showing the same error at the same line

Then the sheet name in the code is different to the name on the sheet.

 

[Sektor]

My code works OK. May be you have protected sheets?

 

[jasonb75]

In addition to the last 2 comments, the code will only work if the data to count is continuous from the specified range.

If the first cell is empty it will always give a result of 2.

If there are any empty cells mid-range then the count will stop at the first empty cell.

 

[Sektor]

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