Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Range using VB
Forgot to say Thanks for the suggestion
Please continue your help. Cheers, Simo -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting or changing a range name | Excel Discussion (Misc queries) | |||
Deleting Range name's listed in the range address box. | Excel Discussion (Misc queries) | |||
Programatically deleting Range | Excel Programming | |||
Deleting range names | Excel Programming | |||
Deleting range not a row | Excel Programming |