Slow code / run code with hidden rows

[owentmoore]

Hi all

I have the following code which searches through each row in turn looking for the first occurence of a comma. It then enters the cell location of this comma in a cell on the same row.

I have approx 1700 rows to search which is taking a long time. But if I filter by only the required cells to hide the cells I don't need then this qty goes down to approx 200 rows.

Is there a way to run this code on visible cells only? I could use advanced filter if necessary but If I can get around this and speed up the code at the same time all the better!!

With ActiveSheet
    Do
        With ActiveSheet.Range(strColQTY10 & lRow1 & ":" & strColQTY & lRow1)

        If lRow1 < lRow2 Then

Range(strColQTY & lRow1).Select

        Set strSearch = .Find(",", AFTER:=ActiveCell, LookIn:=xlValues, LookAt _
                :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

            If Not strSearch Is Nothing Then
                firstaddress = strSearch.Address
                
                lCol2 = Worksheets("Summary Data").Cells.Find(What:="SS Cover Process, Qty", AFTER:=ActiveCell, LookIn:=xlFormulas _
                    , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False).Column
                strColQTY2 = Split(Mid(Columns(lCol2).Address, 2), ":")(0)
    
                Range(strColQTY2 & lRow1).Select
                ActiveCell.Formula = "=" & firstaddress
            Else: GoTo SkipCell
            End If
SkipCell:

        lRow1 = lRow1 + 1
            
        End If

        End With

 

[SkipVought]

Hi,

Yes, SpecialCells property...

With ActiveSheet.Range(strColQTY10 & lRow1 & ":" & strColQTY & lRow1).specialcells(xlcelltypevisible)
...

Skip,

_[red][/red]

 

[owentmoore]

Thanks Skip..

I can see now how it works. Problem is that my code is set up to loop through row 3 ("lRow1") to 1700 ("lRow2"). When I use specialcells it still looks for row 3 and when it's not visible gives the error - "no cells were found".

I've pasted the full code in below so yu'll see where lRow1, lRow2 are called set. Is there a way to read the row numbers of the visible rows into an array for example, and loop through these rows instead of all 1700 rows? I've no experience with arrays so apologies if this is a stupid questions...

   lCol1 = Worksheets("Summary Data").Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column + 1
    strColQTY = Split(Mid(Columns(lCol1).Address, 2), ":")(0)
    
    lRow1 = Worksheets("Summary Data").Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Row + 1

    lRow2 = Worksheets("Summary Data").Cells.Find(What:="", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Row - 1


strColQTY10 = "BE"

With ActiveSheet
    Do
        'With ActiveSheet.Range(strColQTY10 & lRow1 & ":" & strColQTY & lRow1).SpecialCells(xlCellTypeVisible)
        With ActiveSheet.Range(strColQTY10 & ":" & strColQTY & "3").SpecialCells(xlCellTypeVisible)

        If lRow1 < lRow2 Then

Range(strColQTY & lRow1).Select

        Set strSearch = .Find(",", AFTER:=ActiveCell, LookIn:=xlValues, LookAt _
                :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

            If Not strSearch Is Nothing Then
                firstaddress = strSearch.Address
                
                lCol2 = Worksheets("Summary Data").Cells.Find(What:="SS Cover Process, Qty", AFTER:=ActiveCell, LookIn:=xlFormulas _
                    , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False).Column
                strColQTY2 = Split(Mid(Columns(lCol2).Address, 2), ":")(0)
    
                Range(strColQTY2 & lRow1).Select
                ActiveCell.Formula = "=" & firstaddress
            Else: GoTo SkipCell
            End If
SkipCell:

        lRow1 = lRow1 + 1
            
        End If

        End With

    Loop While lRow1 < lRow2
End With

 

[SkipVought]

Either test for the Hidden property of the EntireRow object or use For Each objRange in MyRange.SpecialCells(xlcelltypevisible)

Skip,

_[red][/red]