CODEWITHSUNDEEP
excelvbaselectrange
user2495069
user2495069

Reputation: 351

Select range in a particular sheet in excel vba

I have a question about selecting a range in a particular sheet using excel vba.

I don't know why the following is not working:

Thisworkbook.Sheets("N&A").Range("B4:F16").select

However this works:

Thisworkbook.Sheets("N&A").Activate
ActiveSheet.Range("B4:F16").Select

The VBA code is programmed on "N&A" sheet.

Could anyone let me know what could be the reason?

Thank you!

Upvotes: 7

Views: 95706

Answers (2)

user19236946
user19236946

Reputation: 1

You can try with following code to aviod acriveworksheet:

Sub Clean()


lastrow = Worksheets("REPORT").Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

Dim ws As Worksheet
Set ws = Worksheets("Report")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(2, 1), .Cells(lastrow, 5))
End With

rng.Clear

End Sub

Upvotes: -1

Doug Glancy
Doug Glancy

Reputation: 27488

You've basically answered your own question. Here's an excerpt from Excel 2003 help:

"If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells."

More importantly, remember that it's rarely necessary to use Select in VBA, and it should be avoided if possible.

Upvotes: 11

Related Questions