excel - variable - seleccionar la primera celda de un rango vba

VBA: seleccione la primera celda filtrada y luego avance a la siguiente

Question

Lo que quiero hacer me parece bastante simple, pero no puedo encontrar la forma de hacerlo. Tengo una hoja de cálculo Excel que tiene muchos detalles de contacto, por ejemplo:

    A                 B            C            D                    E
1   Select who you would to like to email:      * Drop down list *
2   Name:            Company:      Role:        Email Address1:      Email Address2:
3   Michael Jackson  Jackson 5     Singer       [email protected]            [email protected]
4   Brian May        Queen         Guitarist    [email protected]         [email protected]
5   Kurt Cobain      Nirvana       Singer       [email protected]       [email protected]
6   Freddie Mercury  Queen         Singer       [email protected]       [email protected]
7   Pat Smear        Nirvana       Guitarist    [email protected]           [email protected]

Un usuario selecciona qué dirección de correo electrónico desea enviar por correo electrónico utilizando la lista desplegable en D1 , por ejemplo, correo electrónico 1, y luego ejecuta una macro que obtiene los destinatarios del correo electrónico en esa columna. Este bit está bien y lo tengo funcionando. El problema es que cuando un usuario aplica un filtro, digamos todos los guitarristas, seleccionará la primera fila filtrada ( C4 ) y luego irá a la siguiente fila en lugar de a la siguiente fila filtrada, por lo que iría a C5 .

Esta es una adaptación del código que estoy usando actualmente:

Sub SendEmail()

Dim objOutlook As Object
Dim objMail As Object
Dim RowsCount As Integer
Dim Index As Integer
Dim Recipients As String
Dim Category As String
Dim CellReference As Integer

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

RowsCount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

Category = Range("D1")
Dim RowLimit As String
If Category = "Email Address1" Then
    CellReference = 4
ElseIf Category = "Email Address2" Then
    CellReference = 5
End If

Index = 0
While Index < RowsCount
    Set EmailAdrs = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, CellReference).Offset(0 + Index, 0)
    Recipients = Recipients & EmailAdrs.Value & ";"
    Index = Index + 1
Wend

 With objMail
    .To = Recipients
    .Subject = "This is the subject"
    .Display
End With

Set objOutlook = Nothing
Set objMail = Nothing

End Sub

Pero esto solo seleccionará la primera celda filtrada y luego cualquiera que sea la celda debajo de eso.

He intentado muchas ideas diferentes, como pasar por las filas que están ocultas:

While Index < RowsCount
   Do While Rows(ActiveCell.Row).Hidden = True
       ''ActiveCell.Offset(1).Select
       Set EmailAdrs = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, CellReference).Offset(0 + Index, 0)
        Recipients = Recipients & EmailAdrs.Value & ";"
        Index = Index + 1
        ActiveCell = ActiveCell.Offset(0 + Index, 0).Select
    Loop
Wend

He intentado atravesar solo las celdas visibles.

He intentado las ideas de otras personas de otra pregunta de StackOverflow ( VBA Ir a la siguiente celda filtrada ):

If ActiveSheet.FilterMode = True Then
With ActiveSheet.AutoFilter.Range
    For Each a In .Offset(1).Resize(.Rows.Count).SpecialCells(xlCellTypeVisible).Areas
        Recipients = Recipients & a(1, CellReference) & ";"
    Next
End With
MsgBox Replace(Recipients, ";;", vbNullString)
End If

Y:

Dim Rng As Range
If Category = Range("S2") Then
CellReference = 10
''Set your range
Set Rng = Range("A1:B2")
ElseIf Category = Range("S3") Then
CellReference = 14
''Set your range
Set Rng = Range("C1:D2")
ElseIf Category = Range("S4") Then
CellReference = 18
''Set your range
Set Rng = Range("F1:G2")
ElseIf Category = Range("S5") Then
 CellReference = 16
''Set your range
Set Rng = Range("H1:J2")
End If

For Each mCell In ThisWorkbook.Sheets("YourSheetName").Range(Rng).SpecialCells(xlCellTypeVisible)
''Get cell address
mAddr = mCell.Address
''Get the address of the cell on the column you need
NewCellAddr = mCell.Offset(0, ColumnsOffset).Address
''Do everything you need
Next mCell

Y varias otras ideas y cosas de otras páginas web, pero parece que no funcionan.

¿Puede alguien ayudarme y tenga en cuenta que soy nuevo en VBA, así que no tengo mucho conocimiento.

Answer 1

Creo que la propiedad Hidden de un rango es lo que quieres. El siguiente código funcionó para mí:

Dim row As Range
For Each row In Range("MyTable").Rows
    If not row.EntireRow.Hidden Then
        '''''' DO STUFF ''''''
    End If
Next

Siempre he encontrado que usar un bucle For Each es una forma mucho más limpia de iterar a través de datos en una hoja de Excel. "MyTable" fue el nombre que le di al rango de interés, pero si lo prefiere, puede ingresar a los límites del rango como Range("A1:D4") . Aunque creo que es una mejor práctica usar rangos con nombre ya que hace que tu código sea más legible.

EDITAR: Para dirigir tu comentario ...

Si inserta una fila en el medio de un rango con nombre, los límites del rango se expanden automáticamente. Aunque si su tabla va a ser el único dato en la hoja de cálculo, también puede usar la propiedad UsedRange de un objeto de hoja de cálculo. Por ejemplo:

Dim row As Range
For Each row In Worksheets("MySheet").UsedRange.Rows
    If not row.EntireRow.Hidden Then
        '''''' DO STUFF ''''''
    End If
Next

Si todo lo que tienes es la primera fila de la tabla, puedes expandir este rango a la tabla completa usando:

dim FirstRow as Range
dim LastRow as Range
dim myTable as Range
set FirstRow = Range("A1:B1")
set LastRow = FirstRow.End(xlDown)
set myTable = Range(FirstRow, LastRow)

Y luego use el mismo bucle For Each que antes. ¡Espero que esto ayude!

Answer 2

Prueba este código:

Sub SendEmail()
    Dim objOutlook As Object
    Dim objMail As Object
    ''Dim RowsCount As Integer
    ''Dim Index As Integer
    Dim Recipients As String
    Dim Category As String
    Dim CellReference As Integer
    Dim RowLimit As String
    ''New variables.
    Dim firstRow As Long
    Dim lastRow As Long
    Dim cell As Excel.Range
    Dim row As Long



    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)


    Category = Range("D1")
    If Category = "Email Address1" Then
        CellReference = 4
    ElseIf Category = "Email Address2" Then
        CellReference = 5
    End If



    With ActiveSheet

        ''Find the first and last index of the visible range.
        firstRow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).row
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).row


        ''Iterate through all the rows between [firstRow] and [lastRow] established before.
        ''Some of those rows are hidden, but we will check it inside this loop.
        For row = firstRow To lastRow

            Set cell = .Cells(row, CellReference)

            ''We are checking here if this row is hidden or visible.
            ''Note that we cannot check the value of property Hidden of a single cell,
            ''since it will generate Run-time error ''1004'' because a single cell cannot be
            ''hidden/visible - only a whole row/column can be hidden/visible.
            ''That is why we need to refer to its .EntireRow property first and after that we
            ''can check its .Hidden property.
            If Not cell.EntireRow.Hidden Then

                ''If the row where [cell] is placed is not hidden, we append the value of [cell]
                ''to variable Recipients.
                Recipients = Recipients & cell.Value & ";"
            End If

        Next row

    End With


    With objMail
        .To = Recipients
        .Subject = "This is the subject"
        .Display
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing

End Sub