VBA-code does not look through hidden rows for adding a row with tracking number
<p>I have another question which I hope to resolve with your help. </p>

<p><strong>What do I want to do.</strong>
I use Excel to track my work, activities, contacts, et cetera. While doing that I found I was doing a lot of repetitive work in adding rows at the end of a sheet called "Activities". </p>

<p>What I want to do is this:
 - Press a button and adding a row.
 - Increase the trackingnumber with 1
 - Insert default values</p>

<p><strong>The code.</strong>
To automate this, I have found (copy, pasted, adjusted it to my needs) the following code:</p>

<pre><code> Sub AddRowActiviteiten_NewAtEnd()
'Add's a new row at the end of the sheet.

Dim wsActiviteiten As Worksheet
Set wsActiviteiten = Sheets("Activiteiten")

DefType = "Daily"
DefStatus = "Open"
DefIssue = "*****"
DefImpact = "*****"
DefPrio = "Laag"
MyDate = Date

wsActiviteiten.Range("A4").Value = "1"

'Copy the "One Row To Rule Them All"
wsActiviteiten.Range("A3:Q3").Copy

wsActiviteiten.Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)

'Stop the "copy-action"
Application.CutCopyMode = False

'Increase the tracking number with "one"
LastNumber = wsActiviteiten.Range("A" &amp; Rows.Count).End(xlUp).Value
wsActiviteiten.Range("A" &amp; Rows.Count).End(xlUp).Offset(1, 0).Value = LastNumber + 1

'Insert default values
LastRow = wsActiviteiten.Range("A" &amp; Rows.Count).End(xlUp).Offset(-1, 0).Row
Cells(LastRow + 1, 2) = DefType
Cells(LastRow + 1, 3) = DefStatus
Cells(LastRow + 1, 4) = DefIssue
Cells(LastRow + 1, 5) = DefImpact
Cells(LastRow + 1, 6) = DefPrio
Cells(LastRow + 1, 8) = MyDate

'Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select

End Sub
</code></pre>

<p><strong>The problem.</strong>
In this sheet I open new items, but I also close them. I do this by changing their status and hide them from view. And this is the point where it goes wrong. When I close the <em>last item</em> on the list and want to add a new row, the macro adds a <em>new row</em> below the <em>last visible</em> entry. It does not find the last entry I have just hidden. And also, when this happens, adding the default values to the new row does not work. It adds them at the row above the added one. </p>

<p>Somehow this makes perfect sense. I tell the macro to look for the last entry, but what I don't understand is why it looks at the last <em>visible</em> entry and why it does not look in the hidden rows. </p>

<p><strong>To replicate.</strong> Copy the code into a sheet (maybe you need to change the name of the sheet) and add a few lines. Put some info in the last row and hide it. Add another few lines and see what happens. </p>

<p><strong>The solution.</strong> Is there a way to resolve this? Maybe there is a smarter way of doing things? I looked into things, but mostly I got results using "("A" &amp; Rows.Count).End(xlUp)". A loop could work, but I am afraid that 1) It does not search through hidden rows and 2) it makes the sheet (somewhat) sluggish. I must say I have tried to make a loop, first I want to see if my first solution is salvageable. </p>

<p>Thank you for your input, if there are any questions please let me know. </p>

<p>Simon
<strong>EDIT: Working code for anyone interested</strong></p>

<pre><code>  Sub AddRowActiviteiten_NewAtEnd()
'Add's a new row at the end of the sheet.

Dim wsActiviteiten As Worksheet
Set wsActiviteiten = Sheets("Activiteiten")

DefType = "Daily"
DefStatus = "Open"
DefIssue = "*****"
DefImpact = "*****"
DefPrio = "Laag"
MyDate = Date

'Copy the One Row To Rule Them All
wsActiviteiten.Range("A3:Q3").Copy

'Offset(y,x)
'De -16 is een getal dat iets doet, maar ik weet niet wat.
wsActiviteiten.Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Offset(1, -16).PasteSpecial (xlPasteAll)

'Stop the "copy-action"
Application.CutCopyMode = False

'Het volgnummer verhogen met 1
'Het laatste getal selecteren (LastNumber) en dan plus 1.
LastNumber = wsActiviteiten.Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Offset(0, -16).Value
wsActiviteiten.Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Offset(1, -16).Value = LastNumber + 1

'Insert default values
LastRow = wsActiviteiten.Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Offset(-1, 0).Row
Cells(LastRow + 1, 2) = DefType
Cells(LastRow + 1, 3) = DefStatus
Cells(LastRow + 1, 4) = DefIssue
Cells(LastRow + 1, 5) = DefImpact
Cells(LastRow + 1, 6) = DefPrio
Cells(LastRow + 1, 8) = MyDate

'Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select

End Sub
</code></pre>