VBA代码不会通过隐藏的行来添加具有跟踪号码的行 [英] VBA-code does not look through hidden rows for adding a row with tracking number
问题描述
我想要做什么。
我使用Excel跟踪我的工作，活动，联系人等。在这样做的时候，我发现我正在做很多重复的工作，在名为活动的工作表末尾添加行。
我想做的是这样：
- 按一个按钮并添加一行。
- 增加跟踪号码1
- 插入默认值
代码。
要自动化，我发现（复制，粘贴，根据需要调整）以下代码：
Sub AddRowActiviteiten_NewAtEnd（）
'Add在工作表末尾添加一个新行。
Dim wsActiviteiten As Worksheet
设置wsActiviteiten = Sheets（Activiteiten）
DefType =Daily
DefStatus =打开
DefIssue =*****
DefImpact =*****
DefPrio =Laag
MyDate = Date
wsActiviteiten.Range （A4）Value =1
'复制One Row To Rule Them All
wsActiviteiten.Range（A3：Q3）。复制
wsActiviteiten.Range（A& Rows.Count）.End（xlUp）.Offset（1，0）.PasteSpecial（xlPasteAll）
'停止复制操作
Application.CutCopyMode = False
'使用一增加跟踪号
LastNumber = wsActiviteiten.Range（A& Rows.Count）.End（xlUp） .Value
wsActiviteiten.Range（A& Rows.Count）.End（xlUp）.Offset（1，0）.Value = LastNumber + 1
'插入默认值
LastRow = wsActiviteiten.Range（A& Rows.Count）.End（xlUp）.Offset（-1，0）.Row
单元格（LastRow + 1，2）= DefType
单元格（LastRow + 1,3）= DefStatus
单元格（LastRow + 1，4 ）= DefIssue
单元格（LastRow + 1，5）= DefImpact
单元格（LastRow + 1,6）= DefPrio
单元格（LastRow + 1,8）= MyDate
'从现在的位置下降1排。
ActiveCell.Offset（1，0）。选择
End Sub
问题。
在此表中，我打开新项目，但我也关闭它们。我通过改变状态并将其隐藏起来来实现。这是错误的地步。当我关闭列表中的最后一个项目，并希望添加一个新行时，该宏会在最后一个可见条目下方添加一个新行。它没有找到我刚刚隐藏的最后一个条目。而且，当发生这种情况时，将默认值添加到新行将不起作用。它将它们添加到添加的列之上。
不知怎的，这是完美的。我告诉宏来寻找最后一个条目，但是我不明白为什么它会看到最后一个可见的条目，以及为什么它不会隐藏行。
要复制。将代码复制到工作表中（可能需要更改工作表的名称），并添加几行。在最后一行放一些信息并隐藏它。再加上几行，看看会发生什么。
解决方案。有没有办法解决这个问题？也许有更聪明的做事方式吗？我调查了一些事情，但大多数情况下我使用（A& Rows.Count）.End（xlUp）得到结果。一个循环可以工作，但是我恐怕1）它不会通过隐藏的行进行搜索，2）它使得页面（有点）迟缓。我必须说我已经试图循环了，首先我想看看我的第一个解决方案是否可抢救。
感谢您的意见，如有任何问题请通知我。
Simon
编辑：任何感兴趣的工作代码
Sub AddRowActiviteiten_NewAtEnd（）
'Add在工作表末尾添加一个新行。
Dim wsActiviteiten As Worksheet
设置wsActiviteiten = Sheets（Activiteiten）
DefType =Daily
DefStatus =打开
DefIssue =*****
DefImpact =*****
DefPrio =Laag
MyDate = Date
'复制一行来统治它们全部
wsActiviteiten.Range（A3：Q3）。复制
'Offset（y，x）
'De -16是een getal dat doet，maar ik weet niet wat。
wsActiviteiten.Range（Split（ActiveSheet.AutoFilter.Range.Address，：）（1））。Offset（1，-16）.PasteSpecial（xlPasteAll）
'停止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
'插入默认值
LastRow = wsActiviteiten.Range（Split（ActiveSheet.AutoFilter .Range.Address，：）（1））。Offset（-1，0）.Row
单元格（LastRow + 1，2）= DefType
单元格（LastRow + 1，3）= DefStatus
单元格（LastRow + 1，4）= DefIssue
单元格（LastRow + 1，5）= DefImpact
单元格（LastRow + 1,6）= DefPrio
单元格（LastRow + 1，8）= MyDate
'从现在的位置下降1行。
ActiveCell.Offset（1，0）。选择
End Sub
更新
我看到你的工作表有一个自动过滤器隐藏状态行 - 其中查找不会检测，不像隐藏的行。
建议您尝试以下更新的代码：
Sub Test ）
Dim rng1 As Range
如果ActiveSheet.AutoFilterMode然后
MsgBox ActiveSheet.Range（Split（ActiveSheet.AutoFilter.Range.Address，：）（1））。 Row
Else
设置rng1 =列（A：A）。Find（*，[a1]，xlFormulas，xlByRows，xlPrevious）
如果不是rng1没有，那么MsgBox rng1.Row
End If
End Sub
初始帖子
如果您隐藏行，则可以使用查找与 xlFormulas 选项来查找隐藏行中的条目（不同于 xlValues ）。
Dim rng1 As Range
设置rng1 =列（A：A）。Find（*，[a1]，xlFormulas，xlByRows，xlPrevious）
MsgBox rng1。地址
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
这篇关于VBA代码不会通过隐藏的行来添加具有跟踪号码的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
