clear data subroutine not working right 1

[navyguy]

Hello, I have written a VB subroutine that is supposed to clear all data from an excel worksheet except the first row which hold some titles. The code is as follows

Sub clear_data(sheet_name As String)
Worksheets(sheet_name).Range(Cells(2, 1), Cells(65536, 256)).ClearContents
End Sub

An example call to this subroutine to clear a worksheet named "rail profile" is as follows:

Call clear_data("rail profile&quot

The subroutine works fine if the sheet is selected but if it is not selected I get a "runtime error '1004' Application.defined or object-defined" error.

Does anyone know why I may be getting this error and how I can fix things so that the subroutine works no matter what worksheet is currently selected.

Thanks

 

[Loomah]

Hi
Don't actually know the (technical) reason why but this should work - or at least it did when I tested it!

Worksheets(sheet_name).Range("A2:IV65536").ClearContents

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[navyguy]

Thanks Loomah, it worked like a charm. I tis puzzling why it works though.