Deleting Range using VB
 

Hi

I was just wondering if someone could tell me what the VB code is for
deleting a range called "Book"

I have used a dynamic name range, and want to be able to clear it using
a reset button

Cheers,
Simon


Sorry if this is easy,
i'm obviously a newbie


---
Message posted from http://www.ExcelForum.com/

Deleting Range using VB
 

The question is are you deleting or clearing?

Range("Book").clearcontents

or

Range("Book").delete (xlUp)

Dunca

--
Message posted from http://www.ExcelForum.com

Deleting Range using VB
 

I tried using delete but for some reason it doesn't work. The code I'
using is:

Private Sub CommandButton1_Click()
ThisWorksheet.Range [ItemN].Delete
ThisWorksheet.Range [Quantity].Delete

End Sub

I will attach the file to show what I mean.
Problems located on "Order Entry Sheet"

B.T.W
I am also having problems with my Process order button. After I hav
added Items to the order I press Process and get an error.
This does not occure if I don't specify a range to paste the copie
range into, but this means that the data could go anywhere on the shee
(last active cell) and not the location I wan

Attachment filename: book catalogue.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56071
--
Message posted from http://www.ExcelForum.com

Deleting Range using VB
 

Forgot to say Thanks for the suggestion
Please continue your help.

Cheers,
Simo

--
Message posted from http://www.ExcelForum.com

Deleting Range using VB
 

Cheers for the thanks, something people often don't do! And is this i
likely to be one of my last posts I'd better make it a good one- I hav
been posting loads on here while doing a job in xl that I basicall
automated after a week for thelast 3 months. I have however got a muc
nicer one starting in a couple of weeks!

When you use the delete command you are deleting the range of cells a
you would if you selected them, right clicked and then selected delete
You are then promted to select which direction you want to move th
remaining cells in (give it a go if you aren't sure what I mean). Thi
means you have to specify this direction when you use delete to giv
"Range(rngToDelete).Delete(-Shift- )" where shift can be xlUp, xlDown
xlLeft & xlRight.

It is worth noting that selecting a range then pressing delete has th
same effect as the "ClearContents" command. The range and all it
formats ar still there it's just the values/formulas that disappear.

I am at my parents house today dog sitting before heading out with som
mates later- Haworth had better watch out! My parents PC, despite bein
about 2GHz faster than mine, is running office 97 and I'm having savin
issues.... I am able to view the code though however, and it looks lik
the problem is only with the select method for that worksheet- I hav
been trying various things and getting bizzare results at time
relating to the active selection. It might prove to be easiest t
delete the sheet and make a new one from scratch as something i
clearly wrong with it.

Some pointers you might find useful though (and they get around no
being able to select cells on the "invoice" worksheet)-

You do not have to use select to complete a paste operation. Where yo
have used:

Range("b3").Select
Selection.Copy
Sheets("invoice").select
Range("b7").select
Activesheet.pase

I would use:

Sheet2.Range("B3").Copy Sheet3.Range("B7")

Also see the code in the attached reating to button2_Click

And finally, though not technically wrong it would be clearer t
customers if in the shipping charge field on the invoice shee
contained the IF() statement rather than the total field. The tota
field can then just sum the 2 rows above it. ie

Sales - 300
Shiping - 25
Total - 300

is more confusing than:

Sales - 300
Shipping - 0
Total - 300

Enjoy

Duncan
uk.geocities.com/duncankarra

Attachment filename: code.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=56077
--
Message posted from http://www.ExcelForum.com

Deleting Range using VB
 

Thank you very much for your help! your help has been invaluable i
assisting me! I will make the changes tonight.

I would buy you a beer if you lived in Australia

Many regards,
Simo

--
Message posted from http://www.ExcelForum.com