KingRaidi
KingRaidi

Reputation: 51

Dynamically count last row and reuse it inside a macro in VBA

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

Answers (2)

KingRaidi
KingRaidi

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

Tim Stack
Tim Stack

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

Related Questions