[SOLVED:] change function add and remove rows

change function add and remove rows

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.

Code:

=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:

Code:

=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.

Code:

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

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. :(

1 Attachment(s)

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.

Code:

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 )

Code:

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.Attachment 14488

Any advices on my Code would be great.

Ok That was easer then expectet ^^

Code:

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

just works fine

I changed this:

Code:

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

Code:

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