Run Macro after filtering

[vzachin]

Hi,
I need to preface this post with 2 comments:
I crossposted my question in vbaexpress but was not successful in getting my problem resolved.
Also, I'm not sure if I should post this question in the Attachmate Solutions or VBA Visual Basics.

Here's my dilemma:
I use Excel to query the mainframe using Extra. Basically, I have 4 columns of data beginning in C5 through F5. The data can be 1 or more row(s) of data. My macro queries the mainframe with the 1st row (C5-F5) and does an update. Then continues to the next row (C6-F6) until it reaches the last empty row when it stops.

The end user wants to filter the information before running the macro.
The coding I received from vbaexpress is as follows:
For Each cell In Range("C5:C65536").SpecialCells(xlCellTypeVisible)
If cell.FormulaR1C1 = "" Then
Exit Sub
End If
Next cell

This only works if I have 1 column of data.

My current coding works without filtering in Excel.When I filter in Excel, the macro doesn't recognize the visble cells and runs as it normally does.
Is it possible to do what I am asking?
Sub State()
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System Is Nothing) Then
MsgBox "Could not create the EXTRA System object. Stopping macro playback."
Stop
End If
Set Sessions = System.Sessions
If (Sessions Is Nothing) Then
MsgBox "Could not create the Sessions collection object. Stopping macro playback."
Stop
End If
g_HostSettleTime = 3000 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then
MsgBox "Could not create the Session object. Stopping macro playback."
Stop
End If
If Not Sess0.Visible Then Sess0.Visible = True
Sess0.Screen.WaitHostQuiet (500)
' This section of code contains the recorded events

Sess0.Screen.SendKeys ("<CLEAR>")
Sess0.Screen.SendKeys ("/FOR EQQA")
Sess0.Screen.SendKeys ("<ENTER>")
Sess0.Screen.WaitHostQuiet (500)

Dim Rw As Long
Rw = 4
Sheets("STATES").Select

For i = 5 To 65536
If Range("C" & i).FormulaR1C1 = "" Then
MsgBox "Completed"
Exit Sub
End If

States = Range("C" & i)
County = Range("D" & i)
City = Range("E" & i)
Zip = Range("F" & i)

Sess0.Screen.PutString States, 2, 11
Sess0.Screen.PutString County, 2, 35
Sess0.Screen.PutString City, 3, 11
Sess0.Screen.PutString Zip, 3, 35
Sess0.Screen.SendKeys ("<PF1>") 'FIND
Sess0.Screen.WaitHostQuiet (1000)
Sess0.Screen.PutString "G", 9, 6
Sess0.Screen.SendKeys ("<PF5>") 'UPDATE
Sess0.Screen.WaitHostQuiet (1000)

Next

End Sub

thanks
zach

 

[CautionMP]

vzachin,
It's been a couple days, but...

...
        For i = 5 To 65536
          If Range("C" & i).FormulaR1C1 = "" Then
            MsgBox "Completed"
            Exit Sub
          [b]ElseIf Not Worksheet("STATES").Rows(i).Hidden Then
            Next i[/b]
          End If
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[vzachin]

Hi CMP,

Thanks so much for your wonderful solution. Definitely better than the one I came up with, which was to copy the filtered data to another worksheet and then run my macro.

thanks again
zach