ExcelBanter - Delete cells.

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Delete cells. (https://www.excelbanter.com/excel-discussion-misc-queries/146683-delete-cells.html)

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

I forgot to add the sheet below.

On sheet called "Client Name Input"
A935:G3014

"Jman" 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

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:

Originally Posted by Jman (Post 509986)

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