Hey guys
I have a worksheet were some of the cells act as titles and headings. How can I make it so that when the page is highlighted and delete is pressed, everything gets deleted except these specific cells. I cant use cell locking because when I highlight everything and press delete, an error message pops up.
You could create a button that clears contents of all cells but the first row:
untested.
Please Login or Register to view this content.
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
How about if I didn't want to select a range but wanted to chose specific cells. For example, B1 and A5
Will the cells always be the same? or change every time you'd like to clear?
They will be the same
How familiar are you with VBA?
How many cells will there be? Which ones? Perhaps it would be easier if you attached a before and after example detailing what you would like to happen.
Like I know how to create buttons and modules but I am not good with VBA. I follow instructions well though. The problem is that I don't actually know the exact cells right now. Is there a template you can give me that will only require for me to put in the cells?
Hopefully you get the idea:
Please Login or Register to view this content.
I think I am almost there. This is the code I run when trying to keep A7 and E7
Sub behnam()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim aryKeepers() As Variant
aryKeepers(1) = Range("A7").Value
aryKeepers(2) = Range("E7").Value
ws.UsedRange.ClearContents
Range("A7").Value = aryKeepers(1)
Range("E7").Value = aryKeepers(2)
End Sub
The problem is it stops at "aryKeepers(1) = Range("A7").Value" and says subscript out of range. Do you know the problem?
you dim ws as active worksheet and then don't use it before your Ranges such as ws.range("A7").Value.
Also in which code module is this code saved in?
Arrays need to be defined before you use them.
Change DIM aryKeepers() as Variant (the size of the Array is not defined therefore you cannot save anything to it yet)
to DIM aryKeepers(2) as Variant (the size of the Array is defined to have 3 "containers" for data: aryKeepers(0), aryKeepers(1), aryKeepers(2)
What jmmach is saying is you have to know how many elements you're putting in the array. I didn't include that because I didn't know if you were adding more than the two ranges to your array.
What I should have put is Dim aryKeepers(x) as variant where x is the number of elements you'll be assigning to the array (0 is the first element index number so 2 numbers would be Dim aryKeepers(1) as variant, Dim aryKeepers(2) would be 3 elements and so forth).
If the number of elements you need is constant you can change x to the number you need. If it will change you can leave x as a variable, set it to different numbers and use a redim statement to change the size of the array.
Thank you guys the code worked perfectly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks