delete 7 cells on each of the 35 sheets

**wwu-dillon** · 08-12-2008, 03:20 PM

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** · 08-12-2008, 03:26 PM

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** · 08-12-2008, 03:35 PM

Maybe

Add extra ranges to array

Dim Arr As Variant
Dim i As Long
Dim Wsht As Worksheet
Arr = Array("C7", "G18", "E14")


For Each Wsht In Worksheets
    For i = 0 To UBound(Arr)
        Wsht.Range(Arr(i)).ClearContents
    Next i
Next Wsht

VBA Noob

**wwu-dillon** · 08-12-2008, 03:35 PM

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** · 08-12-2008, 03:40 PM

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** · 08-12-2008, 03:54 PM

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** · 08-12-2008, 04:08 PM

Add the sheets to ignore on this line in lower case

Case "SHEET1", "SHEET2"

Dim Arr As Variant
Dim i As Long
Dim Wsht As Worksheet
Arr = Array("C7", "G18", "E14")


For Each Wsht In Worksheets
    Select Case UCase(Wsht.Name)
    Case "SHEET1", "SHEET2"
    '
    Case Else
        For i = 0 To UBound(Arr)
            Wsht.Range(Arr(i)).ClearContents
        Next i
    End Select
Next Wsht

VBA Noob