Need Help Selecting Only Visible Cells During Loop VBA

[sax2play]

Hello All,

I have a loop set up that works flawlessly until I attempt to filter a column and select only the visible cells. I have a configuration table that dynamically defines which columns I want to move to a new sheet. Any help allowing this to select only visible cells would be greatly appreciated!

Do While WB.Sheets("Config_Review").Cells(includedCol, 1) <> ""
    
        If WB.Sheets("Config_Review").Cells(includedCol, 1) = "" Then
            Set rngFound = WB.Sheets("EaselBoard_1").Range("1:1").Find(What:=WB.Sheets("Config_Review").Cells(includedCol, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        Else
            colString = WB.Sheets("Config_Review").Cells(includedCol, 1)
            Set rngFound = shEB.Range("1:1").Find(What:=colString, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        End If
                
        If Not rngFound Is Nothing Then
            shNewEB.Range(shNewEB.Cells(1, Col), shNewEB.Cells(last_row, Col)).Value = shEB.Range(shEB.Cells(1, rngFound.Column), shEB.Cells(last_row, rngFound.Column)).Value
            
        Else
            If colString = "" Then
                MsgBox "Cannot find column: " & WB.Sheets("Config_Review").Cells(includedCol, 1)
                End
            Else
                MsgBox "Cannot find column matching: " & colString
                
            End If
            
            Col = Col - 1
                       
        End If
        
        Col = Col + 1
        includedCol = includedCol + 1
        
    Loop

Thanks!!

 

[sax2play]

Nevermind - just needed to step away for a few minutes! I used below:

Do While WB.Sheets("Config_Review").Cells(includedCol, 1) <> ""
    
        If WB.Sheets("Config_Review").Cells(includedCol, 1) = "" Then
            Set rngFound = WB.Sheets("EaselBoard_1").Range("1:1").Find(What:=WB.Sheets("Config_Review").Cells(includedCol, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        Else
            colString = WB.Sheets("Config_Review").Cells(includedCol, 1)
            Set rngFound = shEB.Range("1:1").Find(What:=colString, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        End If
                
        If Not rngFound Is Nothing Then
            last_row2 = shEB.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
            shNewEB.Range(shNewEB.Cells(1, Col), shNewEB.Cells(last_row2, Col)).Value = shEB.Range(shEB.Cells(1, rngFound.Column), shEB.Cells(last_row2, rngFound.Column)).Value
            
        Else
            If colString = "" Then
                MsgBox "Cannot find column: " & WB.Sheets("Config_Review").Cells(includedCol, 1)
                End
            Else
                MsgBox "Cannot find column matching: " & colString
                
            End If
            
            Col = Col - 1
                       
        End If
        
        Col = Col + 1
        includedCol = includedCol + 1
        
    Loop