change function add and remove rows

[MokA]

Hi,

i hope my english is good enough to explain my Problem.

I have a Worksheet with 6 Tables, 4 of them are dynamic. The 2 static tables have 52 columns ( for every week of the year 1 column ) and the 4 dynamic tables have 54 columns ( 52 for calender weeks and 2 for Name declarations )

In the last Table( static ) I add up all the Tables for Column C-BB without a Problem.

 =SUM(geplantInhouse[[#All];[Column3]];geplantResident[[#All];[Column3]];ProjekteInhouse[[#All];[Column3]];ProjekteResident[[#All];[Column3]]

I quickly changed it from english to german hope i didnt make any mistakes there.

Now i want to be able to remove a Row from either .Table("geplantInhouse") or .Table("geplantResident") from the sum without deleting it in the Table.

I thought i might just subtract it again like this:

 =SUMME(geplantInhouse[[#Alle];[Spalte3]];geplantResident[[#Alle];[Spalte3]];ProjekteInhouse[[#Alle];[Spalte3]];ProjekteResident[[#Alle];[Spalte3]];-INDEX(geplantInhouse[#Alle];1;3))

But if i want to add it again ( by removing the subtraction ) this Code doesnt work. Neither does my Range(KW(0)) work, nor does my Seach for "INDEX(geplantInhouse[#All],1,3)" work. If i search e.g. C33 it works.

Sub SummeBearbeiten()

Dim KW
Dim geplant
Dim alteFormel As String
Dim neueFormel As String
Dim i As Integer

KW = Array("INDEX(SummeMA[#Alle];3;1)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;7)", "INDEX(SummeMA[#Alle];3;8)", "INDEX(SummeMA[#Alle];3;9)", "INDEX(SummeMA[#Alle];3;10)", "INDEX(SummeMA[#Alle];3;11)", "INDEX(SummeMA[#Alle];3;12)", "INDEX(SummeMA[#Alle];3;13)", "INDEX(SummeMA[#Alle];3;14)", "INDEX(SummeMA[#Alle];3;15)", "INDEX(SummeMA[#Alle];3;16)", "INDEX(SummeMA[#Alle];3;17)", "INDEX(SummeMA[#Alle];3;18)", "INDEX(SummeMA[#Alle];3;19)", "INDEX(SummeMA[#Alle];3;20)", "INDEX(SummeMA[#Alle];3;21)", "INDEX(SummeMA[#Alle];3;22)", "INDEX(SummeMA[#Alle];3;23)", "INDEX(SummeMA[#Alle];3;24)", "INDEX(SummeMA[#Alle];3;25)", "INDEX(SummeMA[#Alle];3;26)", "INDEX(SummeMA[#Alle];3;27)", "INDEX(SummeMA[#Alle];3;27)", _
     "INDEX(SummeMA[#Alle];3;28)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;37)", "INDEX(SummeMA[#Alle];3;38)", "INDEX(SummeMA[#Alle];3;39)", "INDEX(SummeMA[#Alle];3;40)", "INDEX(SummeMA[#Alle];3;41)", "INDEX(SummeMA[#Alle];3;42)", "INDEX(SummeMA[#Alle];3;43)", _
     "INDEX(SummeMA[#Alle];3;44)", "INDEX(SummeMA[#Alle];3;45)", "INDEX(SummeMA[#Alle];3;46)", "INDEX(SummeMA[#Alle];3;47)", "INDEX(SummeMA[#Alle];3;48)", "INDEX(SummeMA[#Alle];3;49)", "INDEX(SummeMA[#Alle];3;50)", "INDEX(SummeMA[#Alle];3;51)", "INDEX(SummeMA[#Alle];3;52)")

geplant = Array(",-INDEX(geplantInhouse[#All],1,3)")
    
    If Range(KW(0)).HasFormula = True And InStr(1, Range(KW(0)).Formula, geplant(0)) <> 0 Then
    
   
        While i < 52
            alteFormel = Range(KW(i)).Formula
            neueFormel = Replace(alteFormel, "-INDEX(geplantInhouse[#Alle];1;3)", "")
            Range(KW(i)).Formula = neueFormel
           ' i = i + 1
        Wend
        
    Else
        While i < 52
        alteFormel = Range(KW(i)).Formula
        neueFormel = alteFormel + geplant(0)
        Wend
    End If
    


End Sub

And the Problem with my Code is that i would have to create way to many Arrays(didnt even finish the geplant() ) to make it work for Every Row in my 2 tables i want to be able to remove and add from the function.

Maybe you have another Idea to solve my Problem. Because i've been stuck on this issue for far to long now.

Greetings
Mo

[Tommy]

Hi Moka,

I am not sure what you are dong and it is hard for anyone to understand when there is no data and a file to reference. Maybe post a workbook with the confidentiality removed.

Meanwhile, maybe something to look at is subtotals, also review hiding the rows you do not want added. The subtotal should automajically update depending on the "weeks" you want removed/hidden.

No matter how much I love VBA sometimes a formula is better and faster.

[MokA]

Hi Tommy thanks for your reply.

Ok i think i spend to much time with it and was to fixed on myself to be able to explain it properbly, sry for that.

Yesterday Night i just figured another way to do what i want to do. With only one/two Problems.

Sub SelectTest()


Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject



If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then


    While i < 55
    
        helfer.EntireRow.Cells(1, i).Select
        Zelle = ActiveCell.Address(0, 0)
   
        alteFormel = Range(Tabelle).Cells(3, j).Formula
        neueFormel = Replace(alteFormel, "-" & Zelle, "")
        Range(Tabelle).Cells(3, j).Formula = neueFormel
    
        i = i + 1
        j = j + 1
    
    Wend
    
    
Else
    
    While i < 55
    
        helfer.EntireRow.Cells(1, i).Select
        Zelle = ActiveCell.Address(0, 0)
        

        alteFormel = Range(Tabelle).Cells(3, j).Formula
        neueFormel = alteFormel + "-" & Zelle
        Range(Tabelle).Cells(3, j).Formula = neueFormel
    
        i = i + 1
        j = j + 1
    Wend
    
End If

End Sub

The Only 2 things that still bother me are,

1. That i'm Selecting the row before i am able to change the formula. But im Sure i'll find a way around that ( Im pretty new to VBA and Excel )

helfer.EntireRow.Cells(1, i).Select
        Zelle = ActiveCell.Address(0, 0)

2. If i Delete the Row i substracted, i get an error in the SummeMA Table


And i will add my Excel Worksheet so you can see what i am doing.KappaAddRow.xlsm

Any advices on my Code would be great.

[MokA]

Ok That was easer then expectet ^^

Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)

just works fine

[Tommy]

I changed this:

Sub SelectTest()
Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject

If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then

    While i < 55
    
        helfer.EntireRow.Cells(1, i).Select
        Zelle = ActiveCell.Address(0, 0)
   
        alteFormel = Range(Tabelle).Cells(3, j).Formula
        neueFormel = Replace(alteFormel, "-" & Zelle, "")
        Range(Tabelle).Cells(3, j).Formula = neueFormel
    
        i = i + 1
        j = j + 1
    
    Wend
    
Else
    
    While i < 55
    
        helfer.EntireRow.Cells(1, i).Select
        Zelle = ActiveCell.Address(0, 0)

        alteFormel = Range(Tabelle).Cells(3, j).Formula
        neueFormel = alteFormel + "-" & Zelle
        Range(Tabelle).Cells(3, j).Formula = neueFormel
    
        i = i + 1
        j = j + 1
    Wend
    
End If
End Sub

To this

Sub SelectTest()



Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject

If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then

    While i < 55
    
        Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)
        Range(Tabelle).Cells(3, j).Formula = Replace(Range(Tabelle).Cells(3, j).Formula, "-" & Zelle, "")
    
        i = i + 1
        j = j + 1
    
    Wend
    
Else
    
    While i < 55
    
        Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)
        Range(Tabelle).Cells(3, j).Formula = Replace(Range(Tabelle).Cells(3, j).Formula, "-" & Zelle, "")
    
        i = i + 1
        j = j + 1
    Wend
    
End If
End Sub