Solved: xlCellTypeVisible question

[vzachin]

hi,

i have the following coding that works.
but the problem is if the autofiltered data begins in row 20000, it takes a while to get there.
i want to speed things up

Sub test()
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 5 To iLastRow
Part = Range("A" & i)
Stat = Range("B" & i)
Fixa = Range("C" & i)
If Part = "" Then
ElseIf Not Worksheets("Sheet1").Rows(i).Hidden Then
'i have other coding in here that queries a mainframe
'based on Part = Range("A" & i) and then returns
'data to Range("D" & i) = Part and Range("E" & i)
Range("D" & i) = Part
Range("E" & i) = "comp"
End If
Next
End Sub

i found the following code in the forum
but i'm getting stuck trying to figure out how to get the values in column b & c

sub test2()
Dim Part As Range
Dim Stat As Range
Dim Fixa As Range
For Each Part In Range(Cells(5, 1), Cells(5, 1).End(xlDown)).SpecialCells(xlCellTypeVisible).Cells
MsgBox Part.Value
'how do i find the values in column b,c,
'how do i place a value into d,e,...etc
Next Part

End Sub


how can i modify this coding?

thanks
zach

[Bob Phillips]

Sub test2()
Dim LastRow As Long
Dim rng As Range
Dim cell As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A5").Resize(LastRow - 4).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then

For Each cell In rng

cell.Copy cell.Offset(0, 3)
cell.Offset(0, 4).Value = "comp"
Next cell
End If
End Sub

[vzachin]

hi XLD,

thanks for the coding. greatly appreciated

zach