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

Question

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.

With this much code we can only guess what's going on in your macro. Can you provide a [mcve]? — Nacorid, Jul 23 '19 at 14:37
Put this line in the Loop, It should get the Latest row value — Mikku, Jul 23 '19 at 15:07
@KingRaidi You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And reference all your `Range`, `Rows`, `Cells` etw with a sheet! Like `Worksheets("SheetName").Range()` or make sure they start with a dot `.Range` to make sure they use the `With` statement. • Actually your last `With Worksheets("Tabelle3")` is completely usless because none of the statements inside start with a dot `.` so the `With` is not used at all. — Pᴇʜ, Jul 24 '19 at 09:29

score 1 · Answer 1 · answered Jul 24 '19 at 07:35

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

score 1 · Answer 2 · answered Jul 24 '19 at 08:14

1

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

answered Jul 24 '19 at 08:14

KingRaidi

51
9

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

2 Answers2