code does not look through hidden rows for adding a row with tracking number
I have another question which I hope to resolve with your help.
What do I want to do. 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".
What I want to do is this: - Press a button and adding a row. - Increase the trackingnumber with 1 - Insert default values
The code. To automate this, I have found (copy, pasted, adjusted it to my needs) the following 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" & 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" & Rows.Count).End(xlUp).Value
wsActiviteiten.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = LastNumber + 1
'Insert default values
LastRow = wsActiviteiten.Range("A" & 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
The problem. 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 last item on the list and want to add a new row, the macro adds a new row below the last visible 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.
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 visible entry and why it does not look in the hidden rows.
To replicate. 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.
The solution. 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" & 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.
Thank you for your input, if there are any questions please let me know.
Simon EDIT: Working code for anyone interested
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
Update
I see your sheet has an autofilter "hiding" the status rows - which Find wont detect, unlike hidden rows.
Suggest you try this updated code below:
Sub Test()
Dim rng1 As Range
If ActiveSheet.AutoFilterMode Then
MsgBox ActiveSheet.Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Row
Else
Set rng1 = Columns("A:A").Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row
End If
End Sub
initial post
If you are hiding rows then you can use Find with the xlFormulas option to find entries in hidden rows (unlike xlValues ).
Dim rng1 As Range
Set rng1 = Columns("A:A").Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
MsgBox rng1.Address
Say we have a status column AB and we currently close an item by placing the word "Closed" in that column and then hiding the row.
Instead:
使用这个获得最后一行，它会看到最后一行，即使它是隐藏的。
LastRow = wsActiviteiten.UsedRange.Rows.Count