Reputation: 51
inside my macro a table gets bigger every time i repeat a loop. I have to dynamically calculate the value of the last row (its changing with every repetition of the loop). Does anyone know how this could work?
This is the line where I want to get the last row
LastRowITR = .Cells(Rows.Count, 2).End(xlUp).Row
This is the main part of my code
'first row number where you need to paste values in Tabelle3'
With Worksheets("Tabelle3")
j = .Cells(.Rows.Count, 2).End(xlUp).Row
End With
'Remove any pre-existing outlining on worksheet, or you're gonna have 99 problems and an outline ain't 1'
Cells.ClearOutline
For i = 1 To LastRow
With Worksheets("BalanceSheet")
If .Cells(i, 1).Value = "" Then
MsgBox "finished"
Else
.Range("B" & i).Copy Destination:=Worksheets("Tabelle3").Range("B" & j)
'Kopiert Zeile aus altem Sheet ins neue'
'Immer ganzes Blatt sollte noch durch Range ersetzt werden'
X = HypRetrieve(Tabelle3)
'Zoomt auf alle Level in neue Zeile'
X = HypZoomIn(Tabelle3, Range("B" & j), 1, False)
'LastRowITR ist das letzte eingefügte Level'
StartRowITR = j
LastRowITR = .Cells(Rows.Count, 2).End(xlUp).Row
With Worksheets("Tabelle3")
For a = StartRowITR To LastRowITR
Rows(a).Select
Level = Cells(a, 2).IndentLevel
For b = 1 To Level - 1
Selection.Rows.Group
Next b
Next a
j = LastRowITR + 1
End With
End If
End With
Next i
End Sub
This is how I calculate the last row and it works perfectly fine the first time. But not to repeat the changing value inside the macro.
Upvotes: 0
Views: 1500
Reputation: 51
Hey Guys I found the problem,
the answer of Tim Stack brought me the idea. The problem is that my code was counting the row on the wrong sheet, so I all I had to do is to add this around my row counting code
With Worksheets("Tabelle3")
End with
Upvotes: 1
Reputation: 3248
Updating the last row should be done right after each time the last row changes position.
A short and clear example:
With Sheet1
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To 1000
If Left(.Cells(i,"A"), 1) = "T" Then
.Cells(LRow, "B").Value = .Cells(i, "A").Value
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
''Or in case the last row always moves one position down:
''LRow = LRow + 1
End If
Next i
End With
Upvotes: 1