Geek Answers Handbook

VBA code does not scan hidden lines to add line with tracking number

I have one more question that I hope to solve with your help.

What do I want to do. I use Excel to track my work, activities, contacts, etc. While doing this, I found that I was doing a lot of repetitive work of adding lines to the end of a sheet called "Activities".

I want to do the following: - Click the button and add a line. - Increase tracking number with 1 - Insert default values

The code. To automate this, I found (copied, pasted, adjusted it to my needs) the following code:

Sub AddRowActiviteiten_NewAtEnd() 'Add 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

Problem. On this sheet, I open new elements, but I also close them. I do this by changing my status and hiding them from viewing. And this is the moment when he goes wrong. When I close the last item in the list and want to add a new line, the macro adds a new line below the last visible entry. He does not find the last entry I just hid. And also, when this happens, adding defaults to a new line does not work. He adds them to the line above the added one.

Somehow it makes sense. I tell the macro to search for the last record, but I don’t understand why it looks at the last visible record and why it does not look in hidden lines.

Replicate. Copy the code to the sheet (maybe you need to change the name of the sheet) and add a few lines. Put some information on the last line and hide it. Add a few more lines and see what happens.

Decision. Is there any way to solve this problem? Maybe there is a smarter way to do something? I studied things, but basically I got the results using "(" A "and Rows.Count) .End (xlUp)". The loop may work, but I'm afraid that 1) it does not look for hidden lines and 2) makes the sheet (somewhat) sluggish. I must say that I tried to make a cycle, first I want to see if my first decision can be saved.

Thanks for your input, if there are any questions, please let me know.

Simon EDIT: working code for all interested

  Sub AddRowActiviteiten_NewAtEnd() 'Add 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
+7
vba excel-vba excel
source share
3 answers

Update

I see that your worksheet has an autofilter that hides status Find that Find doesn't detect, unlike hidden bars.

Suggest trying 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 recording

If you hide lines, you can use Find with the xlFormulas option to search for entries in hidden lines (unlike xlValues ).

 Dim rng1 As Range Set rng1 = Columns("A:A").Find("*", [a1], xlFormulas, , xlByRows, xlPrevious) MsgBox rng1.Address
+2
source share

Say we have an AB status column, and we are currently closing the item by putting the word “Closed” in that column, and then hiding the row.

Instead

  • Show all rows
  • Make any necessary insertions and changes.
  • Through the loop, hide all lines marked "Closed"
+1
source share

Use this to get the last line, and she will see the last line, even if it is hidden.

 LastRow = wsActiviteiten.UsedRange.Rows.Count
0
source share

More articles:


All Articles