Select the next filtered cell?

TNTScrub

New Member
Joined
Jan 7, 2021
Messages
14
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
So, I have what I hope is a simple issue to resolve. I'm working within IE and through various event handlers and such I have my code set up to automatically perform a task in IE when ever a cell in column "A" is selected. At the end of the code (which you can see below) it advances the selection down one cell which triggers the code to run again. This almost works perfectly. The problem I have is when I filter my list. For instance if I'm only viewing rows 1-5 and 20-30 due to my filters, when the code completes on row 5 it advances down and selects row 6 when I really need it to select row 20. This unfortunately breaks my code because I only want it to run on the rows that I've filtered for viewing. Does anybody know a way of selecting the next filtered cell?


VBA Code:
Sub Cycle_Assignment()
Dim ie As Object
Set ie = GetIE
Dim Enumber As String
Set Enumber = ActiveCell.Offset(0, 3).Value
On Error Resume Next
ie.Visible = True
Dim rCell As Range
Dim sMyString As String
Set rCell = ActiveCell
For Each Cell In rCell

If Len(rCell.Formula) = 0 Then
Application.EnableEvents = False
MsgBox "List Complete"
ie.Quit
'Exit Sub

Else

Application.Wait (Now + TimeValue("0:00:01"))
    ie.Document.all("ddlFacility").Value = "TH"

ie.Document.all("TextBox_TagNum").Value = ActiveCell.Value
Set objButton = ie.Document.all("TextBox_TagNum")
objButton.Focus
objButton.Click
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "~"


Application.Wait (Now + TimeValue("0:00:01"))
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_0").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_1").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_2").Value = Enumber
    ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_3").Value = Enumber
    Application.Wait (Now + TimeValue("0:00:01"))
    ie.Document.all("Button_UpdateAll").Click
    Application.Wait (Now + TimeValue("0:00:01"))
ie.Document.all("Button_Clear").Click
'ie.Quit
Application.Wait (Now + TimeValue("0:00:01"))
ActiveCell.Offset(0, 8).Value = "P"
'Application.SendKeys "{DOWN}" 'This doesnt work at all
ActiveCell.Offset(1, 0).Select
End If

Next
End Sub
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,583
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Add This code :
VBA Code:
If Cell.Entirerow.RowHeight > 0 Then
After
VBA Code:
For Each Cell In rCell

AND ADD End if After End if and Before Next
 
Solution

TNTScrub

New Member
Joined
Jan 7, 2021
Messages
14
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Add This code :
VBA Code:
If Cell.Entirerow.RowHeight > 0 Then
After
VBA Code:
For Each Cell In rCell

AND ADD End if After End if and Before Next
This was a face palm kinda solution. Thank you for filling in where my brain stopped. I had to add an else to keep the code moving but your code worked perfectly. Thank you.

I had to add the following make it move to the next line.

End if
Else
ActiveCell.Offset(1, 0).Select
End if
Next
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,583
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,020
Members
415,946
Latest member
bellerom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top