| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells.
Can any one give me a formula or a macro that will automatically delete
whatever i have in the cells below. I dont have anything specific text or numbers that can be identified in the cell to delete them (inorder to use Auto filter). I just need them blank. Here are the cells i want deleted: On Sheet called "Client Visit Input" B55:N85 B118:N148 B155:N185 B192:N222 B229:N259 B266;N296 B303:N333 B340:N370 B377:N407 B414:N444 B451:N481 B488:N518 On the sheet called "Monthly Income Input" B59 B74 B122 B137 B159 B174 B196 B211 B233 B248 B270 B285 B307 B322 B344 B359 B381 B396 B418 B433 B455 B470 B492 B507 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete cells.
I forgot to add the sheet below.
On sheet called "Client Name Input" A935:G3014 "Jman" wrote: |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete cells.
Jman,
You can copy from here and paste directly into a module in the VBE: Sub DeleteCells Sheets("Client Visit Input").Range ("B55:N85").ClearContents Sheets("Client Visit Input").Range("B118:N148").ClearContents .. .. ' (etc). You fill in the rest. Use Copy/Paste Or Ctrl-drag to expedite .. .. Sheets("Monthly Income Input").Range("B59").ClearContents Sheets("Monthly Income Input").Range("B74").ClearContents 'etc. End Sub The problem is that if you change the layout of the sheet, you'll have a lot of work to do changing the sub to match. Better to name each range, then use Sheets("Client Visit Input").Range ("Range1").ClearContents Sheets("Client Visit Input").Range("Range2").ClearContents etc. Now if you've moved the ranges, or inserted or deleted rows and/or columns within each range, the macro will adjust. -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Jman" wrote in message ... Can any one give me a formula or a macro that will automatically delete whatever i have in the cells below. I dont have anything specific text or numbers that can be identified in the cell to delete them (inorder to use Auto filter). I just need them blank. Here are the cells i want deleted: On Sheet called "Client Visit Input" B55:N85 B118:N148 B155:N185 B192:N222 B229:N259 B266;N296 B303:N333 B340:N370 B377:N407 B414:N444 B451:N481 B488:N518 On the sheet called "Monthly Income Input" B59 B74 B122 B137 B159 B174 B196 B211 B233 B248 B270 B285 B307 B322 B344 B359 B381 B396 B418 B433 B455 B470 B492 B507 |
|
#4
|
|||
|
|||
|
Copy/paste all the addresses for one sheet from this message board to a Word Document.
Use Replace to replace all colons, semi-colons and paragraph marks with a comma. Use Replace to replace all spaces and non-breaking spaces with the null string. This should result in one long string of cell addresses, seperated only by commas. Do this for all sheets. Open Excel and press Record Macro Select a sheet Copy the corresponding string from Word and paste it in Excel's Name Window. Press Return Press Delete Select another sheet Copy the corresponding string from Word and paste it in Excel's Name Window. Press Return Press Delete ... repeat until all sheets are done Stop Recording Quote:
Last edited by mikerickson : June 16th 07 at 04:32 AM |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete cells.
THANKS EARL,, I DID EXACTLY THE WAY YOU RECOMENDED.. IT WORKS PERFECTLY.
IT'S EXACTLY WHAT I NEEDED. THANKS A LOT. I..KEEP UP THE GOOD WORK!! :) ps. i post marked your website. "Earl Kiosterud" wrote: Jman, You can copy from here and paste directly into a module in the VBE: Sub DeleteCells Sheets("Client Visit Input").Range ("B55:N85").ClearContents Sheets("Client Visit Input").Range("B118:N148").ClearContents .. .. ' (etc). You fill in the rest. Use Copy/Paste Or Ctrl-drag to expedite .. .. Sheets("Monthly Income Input").Range("B59").ClearContents Sheets("Monthly Income Input").Range("B74").ClearContents 'etc. End Sub The problem is that if you change the layout of the sheet, you'll have a lot of work to do changing the sub to match. Better to name each range, then use Sheets("Client Visit Input").Range ("Range1").ClearContents Sheets("Client Visit Input").Range("Range2").ClearContents etc. Now if you've moved the ranges, or inserted or deleted rows and/or columns within each range, the macro will adjust. -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Jman" wrote in message ... Can any one give me a formula or a macro that will automatically delete whatever i have in the cells below. I dont have anything specific text or numbers that can be identified in the cell to delete them (inorder to use Auto filter). I just need them blank. Here are the cells i want deleted: On Sheet called "Client Visit Input" B55:N85 B118:N148 B155:N185 B192:N222 B229:N259 B266;N296 B303:N333 B340:N370 B377:N407 B414:N444 B451:N481 B488:N518 On the sheet called "Monthly Income Input" B59 B74 B122 B137 B159 B174 B196 B211 B233 B248 B270 B285 B307 B322 B344 B359 B381 B396 B418 B433 B455 B470 B492 B507 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't delete or add to my cells in excel | Excel Discussion (Misc queries) | |||
| How to delete all the blanc cells in a worksheet and shift cells l | Excel Discussion (Misc queries) | |||
| Delete Cells not Available | Excel Discussion (Misc queries) | |||
| How do I delete duplicate cells? | Excel Worksheet Functions | |||
| Delete repeated Cells | Excel Worksheet Functions | |||
Linear Mode
