Increment to next visible line via vba

[kkjensen]

I'm creating another data file (KML for import into google earth) from data in a table. Pretty basic stuff.

I can't get this loop running because it keeps incrementing by exactly one line. The code works fine except in the scenario where I use excel's filter function to shorten the list.

How can I get this to increment the active cell to the next visible line after the filter is set? I've searched quite a bit and it seems like most people asking "how to go down in a filtered list with vba" are finishing their journey in another direction completely. I need to keep this process rolling for several thousand lines.

<code>
Range("D1").Select <---This is the header where the data filters reside
ActiveCell.Offset(1, 0).Select <-----should be the first visible line but just returns data from line2, even if it's filtered
Do While ActiveCell.Value <> 0
'cellValue = rng.Cells(i, j).Value
ItemType = ActiveCell.Offset(0, 0).Value
ItemName = ActiveCell.Offset(0, 1).Value
ItemCoords = ActiveCell.Offset(0, 2).Value
ItemLength = ActiveCell.Offset(0, 3).Value
ItemBranch = ActiveCell.Offset(0, 4).Value

'blah blah writing text to the file...

ActiveCell.Offset(1, 0).Select
Loop
</code>

A million thanks in advance! I'm a bit rusty on my vba prowess these days...

 

[Kenneth Hobson]

Change to suit. Add the reference object as commented.

Sub Main()
  Dim r As Range, f%, p$
  
  p = Environ("temp") & "\Main.txt"
  
  Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
  r.Copy
  
  f = FreeFile
  Open p For Output As #f
  Print #f, getClipboard
  Close #f
  
  Application.CutCopyMode = False
  [A1].Select
End Sub

'Tools > References... > Microsoft Forms 2.0 Object
Function getClipboard()
    Dim MyData As DataObject
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

 

[Tetra201]

Maybe

For Each cll In Range("D2", Range("D2").End(xlDown)).SpecialCells(xlCellTypeVisible)
    cll.Select
    ' your lines here
Next cll

 

[kkjensen]

Ah...I think that will do it. One of the options I worked through previously was having the user select the range of data to be exported. I wanted to automate it more to dummy-proof it and this should do the trick.

Thanks!

Once it's working, I'll post the final code here so later searchers might find another option.