Solved: run macro after filtering

[vzachin]

hi,
I have a macro that runs with data beginning in Column A5 and will stop when it reaches a blank.
I want to apply a filter and then run the macro. However the macro still begins with A5.
How can I correct my coding so that the macro will run with whatever is filtered?
The filtering can be in Column A or B.
[vba]
Sub State()

For i = 5 To 65536
Sheets("STATES").Select
If Range("A" & i).FormulaR1C1 = "" Then
Exit Sub
End If

Next
End Sub
[/vba]

thanks
zach

[Bob Phillips]

Something like

[vba]

For Each cell In Range("A5:A65536").SpecialCells(xlCellTypeVisible)
'Sheets("STATES").Select
If cell.FormulaR1C1 = "" Then
Exit Sub
End If
Next cell
[/vba]

[mdmackillop]

Check out Visible Cells
[VBA]
Columns("A:A").SpecialCells (xlCellTypeVisible)

[/VBA]

[vzachin]

hi xld,

thanks for the quick reply. i'm having a problem with a Run-time error 1004: Method 'Range' of object'_Global'Failed.
This is where it fails
[VBA]
STATE = Range("A" & i)
[/VBA]

i know i no longer need the i, so i tried the following, which didn't work.
[VBA]
STATE = Range("A5:A65536").SpecialCells(xlCellTypeVisible)
[/VBA]

how can i reference each cell? i use each cell to update data on a mainframe.
[/VBA]
Sess0.Screen.PutString STATE, 3, 43
Sess0.Screen.SendKeys ("<PF1>")
[/VBA]

[vzachin]

i left out the following:
[VBA]
State = Range("A" & i)
City = Range("B" & i)
[/VBA]

how do i reference each cell?

thanks again
zach

[vzachin]

hi xld,

after trial and error, This is what I came up with:

[VBA]
For Each Cell In Range("A5:A65536").SpecialCells(xlCellTypeVisible)
City = Range("B5:B65536").SpecialCells(xlCellTypeVisible)
If Cell.FormulaR1C1 = "" Then
Exit Sub
End If
State = Cell
Sess0.Screen.PutString State, 2, 11
Sess0.Screen.PutString City, 2, 35

Next cell

[/vba]
Here's the problem:
If i filter either column A or Column B, the macro stops after the 1st visible cell.
If i don't filter, the macro gives me an error message here:
[vba]
Sess0.Screen.PutString City, 2, 35
[/vba]

i can't seem to figure this out.

thanks
zach

[Bob Phillips]

I have no idea what

Sess0.Screen.PutString

is, and you give no clue.

[vzachin]

hi xld,

Sess0.Screen.PutString
is how i get information from Excel over to the mainframe application via Extra (Attachmate).
Sess0.Screen is the screen session that is opened.
PutString is the value from Excel that i need to populate in Extra.
2, 35 would be the co-ordinates on the screen (Row2,Col35)

[Bob Phillips]

Well it looks as though you are using that (whatever it is) incorrectly, but as I have no experience of it I don't think I can help any more.

[vzachin]

hi xld,
after sleeping on this problem, i realized that all i had to do was copy the filtered information to another worksheet and run my original code from there.
thanks for pointing me in the right direction with the visible cells.
zach