Can somebody please check this code out and let me know what's wrong with the "Range" selection. When I run the below listed code, it errors out.
I have check throughout the net and I still can't figure out the problem. Thanks in advance for your HELP.
Worksheets("RDH1").Activate Sheets("RDH1").Unprotect "xyz" eRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Range(Cells(9, 1), Cells(eRow, 28)).Select Selection.ClearContents Sheets("RDH1").Protect "xyz"
Perhap you need to "Dim" eRow? I.e.
You don't need to use "Select" and "Selection" try using only the commands.Sub test() Dim eRow As Integer Worksheets("RDH1").Activate Sheets("RDH1").Unprotect "xyz" eRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Range(Cells(9, 1), Cells(eRow, 28)).Select Selection.ClearContents Sheets("RDH1").Protect "xyz" End Sub
AlfRange(Cells(9, 1), Cells(eRow, 28)).ClearContents
Hi, Alf,
IŽd use Long instead of Integer (for the scope of the new worksheets anyhow) - from what I remember from Excel2002 on any Integer value is internally transferred to a Long anyhow.
Ciao,Sub test() Dim eRow As Long With Worksheets("RDH1") .Unprotect "xyz" eRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row .Range(.Cells(9, 1), .Cells(eRow, 28)).ClearContents .Protect "xyz" End With End Sub
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Thanks for the reply. I tried to Dim eRow but I still receive this error:
Run Time Error '1004'
Select Method of Range Class Failed
This one has me baffled!!!!
Hi, Riverridgefarm,
with RTE 1004 IŽd ask you to attach your workbook (or my favourite suggestion: check for merged cells or comments).
Ciao,
Holger
If you start a sub-routine code with Option Explicit, you need to declare all the variables used in the code otherwise you will get an error "variable not defined". Were you getting the same error? If so declare the variable eRow As Long, otherwise your code looks ok.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
The Range looks OK to me.
Try this.
Sub Try() Dim eRow As Long Worksheets("RDH1").Activate ActiveSheet.Unprotect Password:="xyz" eRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Range(Cells(9, 1), Cells(eRow, 28)).ClearContents ActiveSheet.Protect Password:="xyz" End Sub
I tried the latest suggestion. Now I get that the sheet is Protected but the Debug highlights the Range Code again. I looked at Sheet("RDH1") and it is Unprotected. So I Protected it again, reset the Debug, and ran the program again and I still get the same error: Sheet is Protected and the Range Code is highlighted.
Do you have the right password in the code? Capitals maybe
Absolutely no merged cells or comments in the whole sheet.
Hi Holger,
Yes you are right but old habits die hard i.e. I'm used to small ranges so "Integer" with a limit of 32 767 is ok but for an OP question one should use "Long" to be on the safe side.
Alf
Password is correct. Went to the sheet and Protected / Unprotected a couple of times. All lower case.
Just took out the Unprotect code. Went to Sheet "RDH1" and ensured that it was Unprotected. Put a MsgBox in after eRow and it came up with 42 which is correct. Still get the Range Code highlighted and it's still coming up with the error saying that the sheet is protected. WHAT A PROBLEM.......
Upload the sample workbook so that the problem can be solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks