1

basically what this does is it checks each row in ws2 where a column = "Update" then pulls a particular column data and throws it in the corresponding cell in ws1. the first time this was implemented, everything was running smoothly, now for some reason it's taking a bit of time to finish.

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

i want to place a filter in ws1 that way i can minimize the count of rows it needs to check. now i'm pretty sure that the code below WILL NOT IGNORE hidden rows. i need help in adjusting the code to exclude hidden rows when i run the loop.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
wh3resmycar2
  • 191
  • 1
  • 13
  • Simply use Autofilter on Worksheet2 as shown [here](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) and then loop through only that range – Siddharth Rout Aug 12 '16 at 03:36

1 Answers1

3

Try checking for the .EntireRow.Hidden property:

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

Edit: added after comment below for 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

Please try the above and see if it meets your needs

Busy
  • 128
  • 6