delete 7 cells on each of the 35 sheets

[wwu-dillon]

i am going to have 25 sheets for subcontractors in a construction magement workbook. i want to delete 7 cells on each of the 35 sheets. they will all be in the same place on all sheets. ex c7 g18 e14 etc on all 25 sheets. i was wondering if there is a way to create a function that can perform this operation. let me know if you got anything that might help.

[Stuie]

Hi wwu-dillon,

when you say delete the cells, do you mean clear their contents or delete the entire row or column??

either way tho, you can write a macro that will loop through each sheet and proform this fairly easily.

how many cells need clearing or deleting on each sheet?


EDIT: sorry i didnt see the 7 cells part.

can you list the 7 cells that need this and if they are to be deleted or cleared

[VBA Noob]

Maybe

Add extra ranges to array

Please Login or Register  to view this content.

VBA Noob

[wwu-dillon]

the cells are as follows: E19, E52, E118, E151, E184, E217. i thought about naming each of them their own unique name (deleteme1-70 or so) and then using range find in the macro. if there is an easier way i would love it. they can be zeroed out or replaced with a 0. both ways or either way let me know. thanks alot.

[Stuie]

wwu-dillon, i would personally go with what what VBA Noob said as that is the exact same thing i was going to suggest

[wwu-dillon]

its a little confusing but i guess ill play around with it. im not trying to be difficult, this help is amazing and fast. but ill see what i can do. im almost done at work but will be back tomorrow to mess with it.

edit: i will have many more sheets in the workbook so can i set an array for the worksheets, or does this make a big impact.

[VBA Noob]

Add the sheets to ignore on this line in lower case

Please Login or Register  to view this content.

Please Login or Register  to view this content.

VBA Noob

[wwu-dillon]

let me just make sur ei understand everything, and then ill ask another question.

dim arr as variant is saying that arr( which is an array or cells) is the varialble that we will be doing "x" to. (this case deleting)

dim i as long dont understand

dim wsht as worksheet is defining wsht and a worksheet

arr=array(cells) defining our array.

below that it is a little confusing but i understand it as well.

if i wanted to change the way i wrote the code, so that it was every worksheet defined in case. ex Case "sheet1", "sheet2". if i want the worksheets in the case to be the ones that get the array deleted from them how would i change the code.

[VBA Noob]

Links should help and best way to learn

Example of for each sheet
http://www.mvps.org/dmcritchie/excel/sheets.htm

Select Case
http://www.ozgrid.com/VBA/select-case.htm

Some info on dim
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Arrays
http://www.ozgrid.com/VBA/sort-array.htm


VBA Noob

[wwu-dillon]

Please Login or Register  to view this content.

same question as above, sorry for not wraping my code =(

[wwu-dillon]

Please Login or Register  to view this content.

everything is working fine up until the line:

Please Login or Register  to view this content.

that is where i need some help. i want it to delete the cells and when i run the macro it says there is an error in the line :

Please Login or Register  to view this content.

can anyone help me in fixing this issue?

[VBA Noob]

What's the error message

VBA Noob

[Cheeky Charlie]

It's an array or it isn't...

Please Login or Register  to view this content.

[wwu-dillon]

ok i think i got it working. im going to test it with data in the next few days and see how it goes. thank you all for your help.

-dillon