テスト23

'フィルターをかけた後、可視セルの値だけ取得
Sub Kashi_Cell()

Dim r As Variant
Dim rr As Variant
Dim rs As Variant
Dim AAA As Variant

With ThisWorkbook.Sheets("Sheet5")

    .Range("$A$1:$G$40").AutoFilter Field:=1, Criteria1:="神奈川"

    Set r = .Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

    For Each rr In r

        AAA = Split(rr.Address, "$")

        If AAA(2) <> 1 Then
            MsgBox rr.value
            MsgBox .Cells(rr.Row, 6).value
        End If

    Next

End With

End Sub