Excel VBA，for循环忽略隐藏行
Excel VBA，for循环忽略隐藏行
提问于 2016-08-12 10:13:37
基本上，它会检查ws2中的每一行，其中a column = "Update“，然后提取特定的列数据，并将其放入ws1中的相应单元格中。在第一次实现时，一切都很顺利，现在由于某种原因，它需要一点时间才能完成。
Dim LastRow As Long, CurRow As Long, DestRow As Long, DestLast As Long
Dim checkstatus As String
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Dashboard")
Set ws2 = Sheets("TempHRI")
LastRow = ws2.Range("B" & Rows.Count).End(xlUp).Row
DestLast = ws1.Range("E" & Rows.Count).End(xlUp).Row
For CurRow = 2 To LastRow 'Assumes first row has headers
checkstatus = CStr(ws2.Range("AB" & CurRow).Value)
If checkstatus = "UPDATE" Then
'Column that looks up the word "Update" in ws2
If Not ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
DestRow = ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole).Row
End If
ws1.Range("I" & DestRow).Value = ws2.Range("F" & CurRow).Value 'assumes supervisor is in column C in both sheets
End If
Next CurRow我想在ws1中放置一个过滤器，这样我就可以最小化它需要检查的行数。现在我非常确定下面的代码不会忽略隐藏的行。在运行循环时，我需要帮助调整代码以排除隐藏行。
回答 1
Stack Overflow用户
发布于 2016-08-12 10:28:01
尝试检查.EntireRow.Hidden属性：
For CurRow = 2 To LastRow 'Assumes first row has headers
' DO something only if the row is NOT hidden
If ws1.Rows(CurRow).EntireRow.Hidden = False Then
checkstatus = CStr(ws2.Range("AB" & CurRow).Value)
If checkstatus = "UPDATE" Then
'Column that looks up the word "Update" in ws2
If Not ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
DestRow = ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole).Row
End If
ws1.Range("I" & DestRow).Value = ws2.Range("F" & CurRow).Value 'assumes supervisor is in column C in both sheets
End If
End If
Next CurRow编辑:在ws2的下面注释之后添加
For CurRow = 2 To LastRow 'Assumes first row has headers
' DO something only if the row is NOT hidden
If ws1.Rows(CurRow).EntireRow.Hidden = False Then
' Checking ws2 as well, for hidden rows
If ws2.Range("AB" & CurRow).EntireRow.Hidden = False Then
checkstatus = CStr(ws2.Range("AB" & CurRow).Value)
If checkstatus = "UPDATE" Then
'Column that looks up the word "Update" in ws2
If Not ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
DestRow = ws1.Range("E15:E" & DestLast).Find(ws2.Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole).Row
End If
ws1.Range("I" & DestRow).Value = ws2.Range("F" & CurRow).Value 'assumes supervisor is in column C in both sheets
End If
End If
End If
Next CurRow请尝试以上方法，看看是否符合您的需求
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38908816
复制相关文章
相似问题
腾讯云开发者
