Not Able to Select a Range in Excel VBA

Question

I have written a macro in a sheet "A" ,

on change in sheet A , i want to copy a range from sheet "C" and paste in sheet "D"

This flow is fine ,and I am able to acheive it . The problem is when I am trying to select the range in sheet B.

rownum= 83 (I get it dynamically)

colnum = 31 (Number of columns in rownum)

Now I want to copy the 83rd Row till 31st column.

Problem Begins :

 Sheets("B").Range("A83:AO83").Select   ~ works like Charm

As the above is not dynamic ,I want to use

Sheets("B").Range(Cells(rownum,1),Cells(rownum,colnum)).Select  ~ Object Defined Error

Any Ideas !!! I am stuck like for 2 hrs.. its not happening .

This is some part of code , to cause less confusion , I am not posting all the code , The code stops at the last line !! OBJECT DEFINED ERROR .

Dim rownum As Integer
   Dim colnum As Integer

 rownum = Application.Match(searchFE, Sheets("Attendance").Range("D:D"), 0)
 colnum = Cells(rownum, Columns.Count).End(xlToLeft).Column
Sheets("Attendance").Range(Cells(rownum, 1), Cells(rownum, colnum)).Select

both rownum and colnum are getting values .

But you do not need to select it. You can directly perform the operation on that range. You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Sep 10 '15 at 10:51
Your code is working fine in my computer... it returns the error when colnum or rownum are not defined first. — Balinti, Sep 10 '15 at 10:55
Run it in debug mode (pressing f8 when vb screen is open ) and after the first line, point the mouse on rownum (on the code) and see there is a value. — Balinti, Sep 10 '15 at 11:09
add before the last row : MsgBox (rownum * 2 / 2 & " " & colnum * 2 / 2) — Balinti, Sep 10 '15 at 11:15
@Balinti I am getting 80 and 31 , which is exactly the numbers I require according to the data — Mukesh Kumar Singh, Sep 10 '15 at 11:22
You've got a couple of good answers below that explain the lack of explicitly referencing a parent worksheet but another version of the same would be: `Sheets("B").Range("A" & rownum).Resize(1, colnum)` — , Sep 10 '15 at 11:36

score 2 · Accepted Answer · answered Sep 10 '15 at 11:22

Let's have a look at

Sheets("Attendance").Range(Cells(rownum, 1), Cells(rownum, colnum)).Select

The Cells(rownum, 1) is not fully qualified. It is not clearly given from which Sheet the Cells should be taken. So ActiveSheet.Cells(rownum, 1) is assumed. Thats why the above command will fail if Sheets("Attendance") is not the active sheet.

...
With Sheets("Attendance")
 .Range(.Cells(rownum, 1), .Cells(rownum, colnum)).Select
End With
...

score 1 · Answer 2 · answered Sep 10 '15 at 11:30

In my opinion it is just a matter of .address. When you are selecting your range, you need to give the address as input, so basically your only error was to write:

Sheets("B").Range(Cells(rownum,1),Cells(rownum,colnum)).Select

Instead of:

Sheets("B").Range(Cells(rownum,1).Address(0,0),Cells(rownum,colnum).Address(0,0)).Select

I like to write .address(0,0) because it reminds me that it is the absolute address (starting from row and col = 0)

I'm also not sure about:

Sheets("Attendance").Range(Cells(rownum, 1), Cells(rownum, colnum)).Select

Normally I'd write:

Worksheets("Attendance").Range(Cells(rownum, 1).address(0,0), Cells(rownum, colnum).address(0,0)).Select

And be careful to refer to a specific Workbook if you are working with more than one!

score 1 · Answer 3 · answered Sep 10 '15 at 12:02

If you want to get to that last row then use this code

    Sub GetThere()

    Dim sh As Worksheet
    Dim r As Long
    Dim c As Long
    Dim rng As Range

    Set sh = Sheets("Attendance")

    With sh
        r = .Cells(.Rows.Count, "D").End(xlUp).Row
        c = .Cells(r, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(r, 1), .Cells(r, c))
    End With

    Application.Goto Reference:=rng

End Sub

If you actually want to do something with the range such as copy it to a different location, then use this code.

Sub GetThere2()

    Dim sh As Worksheet
    Dim r As Long
    Dim c As Long
    Dim rng As Range

    Set sh = Sheets("Attendance")

    With sh
        r = .Cells(.Rows.Count, "D").End(xlUp).Row
        c = .Cells(r, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(r, 1), .Cells(r, c))
    End With

    rng.Copy Sheets("Sheet2").Range("A1")

End Sub

If you really want to select the range, then you have to make sure the sheet is selected as well.

Sub GetThere3()

    Dim sh As Worksheet
    Dim r As Long
    Dim c As Long
    Dim rng As Range

    Set sh = Sheets("Attendance")

    With sh
        r = .Cells(.Rows.Count, "D").End(xlUp).Row
        c = .Cells(r, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(r, 1), .Cells(r, c))
        .Select
    End With

    rng.Select

End Sub

Not Able to Select a Range in Excel VBA

3 Answers3