variable i=i 1 for visible cells-CodePudding

I would like to ask. I have a code that works perfectly. The problem comes when the user filters the data in Excel. Some rows will be hidden. Then the variable i = i 1 does not work. (Works but runs through all lines) Basically, I'm asking how can I set a variable for visible rows only? Thank you for help!

Do
 'If Sheets("FORMAT").Cells(i, 7) Like "*IE-12-2007984*" Then
  'End If
  On Error GoTo Handler
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/ctxtI_VBUKR").Text = "1200"
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Stavby").Cells(i, 7)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "AKTUALIZACE"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Stavby").Cells(i, 2).Value = myGrid.getcellvalue(0, "TXT04")
session.findById("wnd[0]/tbar[0]/btn[3]").press
Handler2:
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Stavby").Cells(i, 7)

i = i   1
If Sheets("Stavby").Cells(i, 7).Value = "" Then Exit Do
Loop
If Sheets("Stavby").Cells(i, 7).Value = "" Then

Set session = Nothing
Connection.CloseSession ("ses[0]")
Set Connection = Nothing
Set SAP = Nothing
MsgBox "HOTOVO"
Exit Sub
Handler:
 Resume Handler2
 End If
End Sub```

CodePudding user response：

if your write just after "do"

   If Not ActiveSheet.rows(i).Hidden then
     goto endOfLoop
   EndIf

and then at the end just before "loop"

EndOfLoop:

none of the code in between will run for hidden rows, but you will need to revise your loop condition as, if you do not increment i, you will have an infinite loop

CodePudding user response：

SOME ROWS Here is just couple of rows. I asked fot the wrong question maybe. So I would like to get value from first visible row and continue to next one visible rows and again. I tried what u wrote here. And still doesn't work. Here is What I did, (I didn't revise loop, just for now) I'm getting now value from first hidden row and continue 1 row.

Do
  If Not ActiveSheet.Rows(i).Hidden Then
     GoTo EndOfLoop
   End If
 'If Sheets("FORMAT").Cells(i, 7) Like "*IE-12-2007984*" Then
  'End If
  On Error GoTo Handler
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/ctxtI_VBUKR").Text = "1200"
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Stavby").Cells(i, 7)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "AKTUALIZACE"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Stavby").Cells(i, 2).Value = myGrid.getcellvalue(0, "TXT04")

session.findById("wnd[0]/tbar[0]/btn[3]").press
Handler2:
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Stavby").Cells(i, 7)
i = i   1
If Sheets("Stavby").Cells(i, 7).Value = "" Then Exit Do
EndOfLoop:
Loop
If Sheets("Stavby").Cells(i, 7).Value = "" Then

Set session = Nothing
Connection.CloseSession ("ses[0]")
Set Connection = Nothing
Set SAP = Nothing
MsgBox "HOTOVO"
Exit Sub
Handler:
 Resume Handler2
 End If
End Sub