VBA Loop with Hidden Rows

[marius29]

Hello all,

New to the community and VBA and I'm having some difficulty figuring this out. If anyone can provide some help I would greatly appreciate it. I have a loop and I need rows that are hidden or filtered out to be skipped. Everything is running fine but rows that are hidden or filtered out are still not ignored.

I have also tried to use (xlCellTypeVisible) instead of RowHeight > 0 in order to get this result but still not luck. Additionally, I I tried using Else Next X and still hasn't worked, I believe that there is a simple answer to resolve this but haven't been able to figure it out yet.

---

Sub Email_Team()

For x = 2 To 60

If Cells(x, 13) < Date - 360 And ActiveSheet.Rows("2,60").RowHeight > 0 Then
Dim Email_Subject, Email_Send_From, Email_Send_To, _​

Email_Cc, Email_Bcc, Email_Body As String​

Dim Mail_Object, Mail_Single As Variant​

Email_Subject = "PCI AoC - " + Cells(x, 3)
Email_Send_From = "email@mail.com"
Email_Send_To = Cells(x, 8)
Email_Cc = "email@mail.com"
Email_Body = "Hello " + Range("F2") + ", " + "We are looking for the latest AoC for " + Cells(x, 3) + ""​

On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
​

With Mail_Single
.Subject = Email_Subject
.to = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Bodymm
.Display​

End With​

End If
Next x
debugs:​

End Sub

---

 

[Michael M]

Maybe this .....UNTESTED

Sub MM1()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
For x = 2 To 60
    [color=red]If Not Rows(x).Hidden Then[/color]
        If Cells(x, 13) < Date - 360 And ActiveSheet.Rows("2,60").RowHeight > 0 Then
            Email_Subject = "PCI AoC - " + Cells(x, 3)
            Email_Send_From = "email@mail.com"
            Email_Send_To = Cells(x, 8)
            Email_Cc = "email@mail.com"
            Email_Body = "Hello " + Range("F2") + ", " + "We are looking for the latest AoC for " + Cells(x, 3) + ""
            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
                With Mail_Single
                    .Subject = Email_Subject
                    .to = Email_Send_To
                    .cc = Email_Cc
                    .BCC = Email_Bcc
                    .Body = Email_Bodymm
                    .Display
                End With
        End If
     [color=red]End If[/color]
Next x
debugs:
End Sub

 

[marius29]

Excellent! Thank you so very much kind Sir! It works perfectly. I will make sure to pay it forward! Thank you again.

 

[Michael M]

glad to help...