Error setting a Range as an object using .End(XlToRight) function

Question

I'm trying to count the number of cells within a specified Range stored in an object called "days", but I get the

1004 error

when I run the following code to get it:

Sub test2()

Dim ncol As Integer
Dim days As Range

Worksheets(1).Activate
ActiveSheet.Range("B1").Select
Set days = Range(Selection, Selection.End(xlToRight))
ncol = days.Count

End Sub

The error occurs when setting "days" Range

I've revised the code many times but I don't find any mistake. Could anyone help me?

I've modified my code to refer it to a Range of data and it keeps to fail:

Sub test2()

Dim ncol As Integer
Dim days As Range

Worksheets("2017").Activate
ActiveSheet.Range("B2").Select
Set days = Range(Selection, Selection.End(xlToRight))
ncol = days.Count

End Sub

I've eventualy solved this problem creating the same subroutine as an object of "2017" sheet instead of a one in the workbook in general. Thanks both of you for help

Answer 1

End(xlToRight) will work only if you actualy have data in your cells.

Note: you don't ned to use Activate, Select or Selection.

Modified Code

Sub test2()

Dim ncol As Integer
Dim days As Range

With Sheets(1)
    Set days = .Range("B1", .Range("B1").End(xlToRight))
    ncol = days.Columns.Count ' if you want to get number of columns
    ncol = days.Cells.Count ' if you want to get number of cells (in your case it's the same)
End With

End Sub

Answer 2

You code will work if data is present:

Sub test2()

Dim ncol As Integer
Dim days As Range

Worksheets(1).Activate
ActiveSheet.Range("B1").Select
Set days = Range(Selection, Selection.End(xlToRight))
ncol = days.Count

MsgBox days.Address(0, 0) & vbCrLf & ncol

End Sub