CODEWITHSUNDEEP
excelvbaloopsexcel-tableslistobject
Sarah
Sarah

Reputation: 11

For-loops - Compile error - comparing data using concatenate

I am pulling data from an online database, but some documents are not able to be completed but cannot be removed from the database. We are creating a table to send to managers telling them which documents need to be completed, but want to make sure that those exceptions don't show up on the table we send them.

I want this section of code to compare the rows to see if they match, and if they do, to hide the row. Currently, I have it setting the cells in each row as a continuous string, then comparing it to our "Exceptions Table" which has a hidden column that concatenates the same data. It should take each row, see if it's on the exceptions table, and hide it if true.

I get a Compile Error: For control variable already in use.

'hide exceptions
For Each Row In ActiveSheet.ListObjects("ISP_Table")
currentrow = Range("ISP_Table")
Set documentinfo = Range("ISP_Table[Individual]").Value & Range("ISP_Table[Activity]").Value & Range("ISP_Table[Due Date]").Value & Range("ISP_Table[Status]").Value
'match it
For Each Row In ActiveSheet.ListObjects("ExceptionsTable")
If StrComp(documentinfo, Range("ExceptionsTable[Exception Info]"), vbTextCompare) = 0 Then
'hide it
EntireRow.Hide

End If
Next Row

Next Row

Upvotes: 1

Views: 44

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Hide Matching Rows

  • By using variables, the code becomes more readable.
  • Application.Match works for a row or a column and is certainly faster than a loop.
Option Explicit
 
Sub HideMatchingRows()
 
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim stbl As ListObject: Set stbl = ws.ListObjects("ExceptionsTable")
    Dim srg As Range: Set srg = stbl.ListColumns("Exception Info").DataBodyRange

    Dim dtbl As ListObject: Set dtbl = ws.ListObjects("ISP_Table")
    Dim drg As Range: Set drg = dtbl.DataBodyRange
    Dim c1 As Long: c1 = dtbl.ListColumns("Individual").Index
    Dim c2 As Long: c2 = dtbl.ListColumns("Activity").Index
    Dim c3 As Long: c3 = dtbl.ListColumns("Due Date").Index
    Dim c4 As Long: c4 = dtbl.ListColumns("Status").Index
    
    Application.ScreenUpdating = False
    
    drg.EntireRow.Hidden = False ' first unhide all rows...
    
    Dim drrg As Range
    Dim DocumentInfo As String
    
    For Each drrg In drg.Rows
        DocumentInfo _
             = CStr(drrg.Cells(c1).Value) & CStr(drrg.Cells(c2).Value) _
             & CStr(drrg.Cells(c3).Value) & CStr(drrg.Cells(c4).Value)
        If IsNumeric(Application.Match(DocumentInfo, srg, 0)) Then
            drrg.EntireRow.Hidden = True ' ... then hide the ones you need            
        End If
    Next drrg

    Application.ScreenUpdating = True

    MsgBox "Matching rows hidden.", vbInformation

End Sub

Upvotes: 1

Related Questions