AutoFilter-SpecialCellsの使用

5
JvdV 2019-11-16 09:56.

バックグラウンド:

何度も応募してきました https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilterなぜそれが時々のように機能するのか、本当に自問することはありませんでした。フィルタリングされたデータの結果を操作することは、特に関係するときに、混乱を招くことがありますSpecialCells

以下のシナリオについて詳しく説明します。


テストデータ:

| Header1 | Header2 |
|---------|---------|
| 50      |         |
| 10      |         |
| 30      |         |
| 40      |         |
| 20      |         |

コード1-プレーンAutoFilter

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Value = "Check"
    .AutoFilter
End With

これは(を使用しなくてもSpecialCells(12))機能しますが、入力されB1ます。


コード2-使用.Offset

上記の動作を防ぐために、次のように実装できますOffset

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Value = "Check"
    .AutoFilter
End With

ただし、これにより、データの下の行、セルにデータが入力されますB7


コード3-使用.Resize

.Offset移入を防ぐためにB7.Resize:を含める必要があります。

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

今では、私たちは予防しB1B7人口を増やすことができましたB2:B6が、AutoFilterメカニズムは「壊れている」ように見えます。以下のスクリーンショットで見せてみました。真ん中のものはフィルタリングされたときで">30"あり、右のものはフィルタリングされ">50"たときです。私が見ているように、これは、参照される範囲がゼロの可視セルで構成されているという事実と関係があります。


コード4-使用.SpecialCells

私がここで行う通常のことは、Count最初に表示されているセル(を防ぐために範囲内のヘッダーを含むerror 1004)に対して行うことです。

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With


質問:

ご覧のとおり、上書きされないようにするために、私は.Columns(2).Value = "Check"ずっとからに行きました。If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"B1

どうやら、AutoFilterメカニズムは目に見える行自体を検出する最初のシナリオで非常にうまく機能しますが、ヘッダーが上書きされるのを防ぐために、私は実装する必要がありました:

  • https://docs.microsoft.com/en-us/office/vba/api/excel.range.offset
  • https://docs.microsoft.com/en-us/office/vba/api/excel.range.resize
  • https://docs.microsoft.com/en-us/office/vba/api/excel.range.specialcells

私はここで物事を複雑にしすぎていますか?より短いルートがありますか?また、セルが表示されなくなったときに、表示されていないセルの全範囲にデータが入力されるのはなぜですか。実際にフィルタリングされたデータがある場合はうまく機能します。これはどのようなメカニズムですか(コード3を参照)?

私が思いついた、それほどエレガントではない(IMO)オプションは、書き直すことB1です。

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        Var = .Cells(1, 2): .Columns(2).Value = "Check": .Cells(1, 2) = Var
    .AutoFilter
End With

8 answers

6
rohrl77 2019-11-20 18:58.

Excelがワークシートにフィルター処理されたリストを作成するたびに、名前マネージャーのバックグラウンドで非表示の名前付き範囲が作成されます。名前マネージャーを呼び出すと、この範囲は通常表示されません。以下のコードを使用して、非表示の名前付き範囲を名前マネージャーに表示します(使用する前に、範囲にフィルターを設定します)。

Dim nvar As Name
For Each n In ActiveWorkbook.Names
    n.Visible = True
Next

英語版のExcelでは、非表示のフィルター範囲は呼ばれます_FilterDatabase。私のソリューションでは、この非表示の範囲をSpeciallCells(12)と組み合わせて使用​​して問題を解決します。

更新私の最終的な答えは隠された名前付き範囲を使用していませんが、それは発見プロセスの一部だったので、私はその情報を残しています...

Sub test1()
Dim var As Range
Dim i As Long, ans As Long
With Sheets("Sheet1").Range("A1:C1")
    .Range("B2:B6").Clear
    .AutoFilter
    .AutoFilter 1, ">50"
        Set var = Sheet1.AutoFilter.Range
        Set var = Intersect(var.SpecialCells(12), var.Offset(1, 0))
        If Not (var Is Nothing) Then
            For i = 1 To var.Areas.Count
                var.Areas(i).Offset(0, 1).Resize(var.Areas(i).Rows.Count, 1).Value = "Check"
            Next i
        End If
    .AutoFilter
End With
End Sub

私はそれを> 30と> 50でテストしました。期待どおりに動作します。

1
Tragamor 2019-11-20 00:30.

問題は明らかにテーブル内の非表示の行を処理することに起因しているため、これを処理する最も簡単な方法は、表示されているセルを操作および確認できるテーブル本体の範囲を作成することです。

表示されている行にマークを付ける場合は、非表示の行よりも少し簡単です。そうでない場合は、ダミー変数を作成し、再表示して、空白を埋めてから、ダミー変数を削除する必要があります。

例えば

Sub AutoFilterTable()

    Dim SrcRange As Range: Set SrcRange = Sheets("Sheet1").Range("A1:B6")
    Dim BodyRange As Range: Set BodyRange = Application.Intersect(SrcRange, SrcRange.Offset(1, 0))

    With SrcRange
        BodyRange.Columns(2).ClearContents
        .AutoFilter 1, ">30"
        On Error Resume Next
        BodyRange.Columns(2).SpecialCells(xlCellTypeVisible) = "Check"
        .AutoFilter
    End With

End Sub

ダミー変数の使用

Sub AutoFilterTable()

    Dim SrcRange As Range: Set SrcRange = Sheets("Sheet1").Range("A1:B6")
    Dim BodyRange As Range: Set BodyRange = Application.Intersect(SrcRange, SrcRange.Offset(1, 0))

    With SrcRange
        BodyRange.Columns(2).ClearContents
        .AutoFilter 1, ">30"
        On Error Resume Next
        BodyRange.Columns(2).SpecialCells(xlCellTypeVisible) = "Dummy"
        .AutoFilter
        BodyRange.Columns(2).SpecialCells(xlCellTypeBlanks) = "Check"
        BodyRange.Columns(2).Replace "Dummy", ""
    End With

End Sub

次に、コード3に関する質問:.Columns(2).Offset(1)が非表示の行であるかどうか(および他の行が非表示であるかどうか)によって異なります。

表示されている場合は、期待どおりに機能します。実際、非表示であるかどうかに関係なく、表示されている行が存在する場合、それらの上にサイズを変更すると、表示されているセルが選択されることになります。ただし、すべての行が非表示になっている場合でも、オフセット範囲は「アクティブ」であるため、範囲を含めるために表示されているセルがない状態でサイズを変更すると、すべてのセルが選択されます。

1
EEM 2019-11-21 14:03.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter範囲にフィルター基準を適用しているため、期待どおりに実行されています。 https://docs.microsoft.com/en-us/office/vba/api/excel.range.specialcellsまた、適用された範囲検出された可視セルとの結合範囲を返すため、期待どおりに実行されます。

SpecialCellsメソッドが範囲全体に適用されると、予期しない結果が生成されRange("A1:B6")ます。ヘッダーが表示されると、結果の範囲に含まれます。

このSpecialCellsメソッドはユニオン範囲(いくつかの領域)を返す可能性Rangeがあるため、値「Check」で更新する必要がある「ターゲット」に適用することをお勧めしますColumn(2)

また、を使用して、表示されている行が返されないOn Error statement場合を管理する必要がありAutoFilterます。

次の手順では、両方のフィルターを適用して、それぞれの値を更新します。

Sub Range_AutoFilter()

    With ThisWorkbook.Sheets(1).Range("A1:B6")

        .AutoFilter 1, ">30"
        On Error Resume Next 
        .Cells(2, 2).Resize(-1 + .Rows.Count) _
            .SpecialCells(xlCellTypeVisible).Value2 = "Check >30"
        On Error GoTo 0
        .AutoFilter

        .AutoFilter 1, ">50"
        On Error Resume Next
        .Cells(2, 2).Resize(-1 + .Rows.Count, 1) _
            .SpecialCells(xlCellTypeVisible).Value2 = "Check >50"
        On Error GoTo 0
        .AutoFilter

    End With

    End Sub

これは他の回答と変わらず、メソッドの適用方法による「予期しない結果」についての説明を追加したかっただけです。

1
Zac 2019-11-25 19:59.

ここには賢いアプローチがたくさんあります。鉱山はもう少し古風ですが、うまくいくようです(質問で提供された表でテストしました)

Sub SetFilteredCell()

    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet4")               ' Change sheet reference
    Dim iLRow As Long: iLRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row        ' Presuming that first row is the header
    Dim oRng As Range: Set oRng = oWS.Range("A1:B" & iLRow)                         ' Set range here
    Dim rFilteredRng As Range
    Dim oCRng As Range

    ' Clear any existing filter
    oWS.AutoFilterMode = False

    ' Set autofilter
    oRng.AutoFilter Field:=1, Criteria1:=">20"

    ' Check if autofilter returned any rows
    If oWS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then

        ' Set filtered range
        Set rFilteredRng = oRng.Cells.SpecialCells(xlCellTypeVisible)

        ' Loop through all filtered rows
        For Each oCRng In rFilteredRng.Rows

            ' Skipping first row as the presumption is that its the header row
            If oCRng.Row > 1 Then
                oWS.Cells(oCRng.Row, 2).Value = "Check"
            End If

        Next

    End If

    ' Clear filter
    oWS.AutoFilterMode = False

End Sub

参照: https://stackoverflow.com/questions/17285897/row-count-on-the-filtered-data

0
Aprillion 2019-11-20 06:11.

これは、これらの手順を手動で実行する場合とまったく同じ動作です。

  • オートフィルターを範囲に適用する
  • その範囲の2番目の列を選択します(一番上の行を含む)
  • すべてを除外します(したがって、一番上の行のみが表示されます)
  • 新しい値を入力し、Ctrl+で範囲全体に挿入しますEnter(2列目の一番上の行のみが影響を受けます)

ここで、入力する前に下矢印を押すと(と同じ.Offset(1))、次に表示されているセルが選択されます(B7)。

B2:B6オートフィルターを適用する前に手動で範囲を選択し(すべてのセルがフィルターで除外されるように)、Ctrl+Enterで値を挿入すると、すべてのセルが影響を受けます-手動オートフィルターの未処理のエッジケースだったと思います(人々は値を挿入しようとしませんでした) VBAオートフィルターを使用する場合は理想的ではありませんが、非表示のセルのみ)。

0
TinMan 2019-11-22 08:17.

これは、ターゲット範囲をチェーンSpecialCells xlCellTypeConstantsxlCellTypeVisibleてトリミングする別のバリエーションです。

With Range("A1:B6")

    .Offset(1).Columns(2).ClearContents
    .AutoFilter 1, ">50", , , True

    On Error Resume Next
     .Offset(1).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).Columns(2) = "Checked"
    On Error GoTo 0

    .AutoFilter
End With
0
PatricK 2019-11-26 08:08.

変更したい場合はdatabodyフィルタ後の範囲のを、あなたが使用する必要があるIntersection1行目で元のオフセット範囲(ヘッダーを除外する)とSpecialCells(xlCellTypeVisible)、その後、通過しますAreas

手がかりはこの例にあります:

Option Explicit

Sub MoreThan50()
    MoreThanValue "50"
End Sub

Private Sub MoreThanValue(Optional Amount As String = "")
    Dim oRng As Range, oRngArea As Range, oRngResult As Range
    Set oRng = Sheets(1).Range("A1:B6")
    ' Clear Previous data on 2nd column
    With Intersect(oRng, oRng.Offset(1))
        .Columns(2).ClearContents
    End With
    With oRng
        ' Apply AutoFilter
        .AutoFilter 1, ">" & IIf(Len(Amount) = 0, "50", Amount)
        ' Update 2nd Column of resulting data
        Set oRngResult = Intersect(oRng.Offset(1), .SpecialCells(xlCellTypeVisible))
        If Not oRngResult Is Nothing Then
            With oRngResult
                If .Areas.Count > 0 Then
                    For Each oRngArea In .Areas
                        oRngArea.Columns(2).Value = "check"
                    Next
                End If
            End With
            Set oRngResult = Nothing
        End If
        .AutoFilter
    End With
    Set oRng = Nothing
End Sub
0
EvR 2019-11-26 17:55.

(限定的なテストでは、列2のセルは空である必要があります).FindNextを使用できると思います。SpecialCellsやエラーステートメントは必要ありません。

これをコード3に追加しました:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
       If Not .FindNext(.Cells(1)) Is Nothing Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

編集: FilterTableの横に空白の列があると仮定します

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">30"
          If Not .Offset(, 1).FindNext() Is Nothing Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

Related questions

MORE COOL STUFF

ケイト・ブランシェットは3日間一緒に夫と一緒に寝て、25年経ってもまだ夫と結婚しています

ケイト・ブランシェットは3日間一緒に夫と一緒に寝て、25年経ってもまだ夫と結婚しています

ケイト・ブランシェットは、夫に会ったとき、典型的な交際のアドバイスに逆らいました。

マイケルシーンが非営利の俳優である理由

マイケルシーンが非営利の俳優である理由

マイケルシーンは非営利の俳優ですが、それは正確にはどういう意味ですか?

ホールマークスターのコリンエッグレスフィールドがRomaDramaLiveでスリル満点のファンと出会う![エクスクルーシブ]

ホールマークスターのコリンエッグレスフィールドがRomaDramaLiveでスリル満点のファンと出会う![エクスクルーシブ]

特徴的なスターのコリン・エッグレスフィールドは、RomaDrama Liveでのスリル満点のファンとの出会いについて料理しました!加えて、大会での彼のINSPIREプログラム。

「たどりつけば」をオンラインでストリーミングできない理由

「たどりつけば」をオンラインでストリーミングできない理由

ノーザンエクスポージャーが90年代の最も人気のある番組の1つになった理由を確認するには、Blu-rayまたはDVDプレーヤーをほこりで払う必要があります。

バイオニック読書はあなたをより速く読むことができますか?

バイオニック読書はあなたをより速く読むことができますか?

BionicReadingアプリの人気が爆発的に高まっています。しかし、それは本当にあなたを速読術にすることができますか?

ドミニカのボイリング湖:アクセスは簡単ではありませんが、ハイキングする価値があります

ドミニカのボイリング湖:アクセスは簡単ではありませんが、ハイキングする価値があります

ドミニカのボイリング湖は、世界で2番目に大きいボイリング湖です。そこにたどり着くまでのトレッキングは大変で長いですが、努力する価値は十分にあります。

私たちの水をきれいに保つのを助けるためにあなたの髪を寄付してください

私たちの水をきれいに保つのを助けるためにあなたの髪を寄付してください

サロンからのヘアトリミングや個人的な寄付は、油流出を吸収して環境を保護するのに役立つマットとして再利用できます。

ホワイトハウスの最も記憶に残る結婚式を見てください

ホワイトハウスの最も記憶に残る結婚式を見てください

過去200年以上の間にホワイトハウスで結婚したのはほんの数人です。彼らは誰でしたか、そしてそこで結婚式を獲得するために何が必要ですか?

ジェイ・ブルースはどうやら子供を妊娠することによってメッツから離れて取引されていることを祝った

ジェイ・ブルースはどうやら子供を妊娠することによってメッツから離れて取引されていることを祝った

あなたが一時的に会っていなかったとき。シーズン11-1を開始したチームであるニューヨークメッツは、日曜日の午後にフィラデルフィアで行われた最後の11試合の9試合目を失いました。

スティーブンキングのアウトサイダーはトランプ時代のそれです

スティーブンキングのアウトサイダーはトランプ時代のそれです

スティーブン・キングのアウトサイダーは、多くの点で先祖返りの小説であり、80年代の全盛期から引き裂かれたように見える生き物の特徴であり、おそらくセル以来の彼の最もパルプのような本ですが、今日の恐怖の中で間違いなく設立された作品です。表面上は、形を変えるペニーワイズのような子供たちの殺人者を中心としており、その最も暗い脅威は、封じ込められず、神経質に平凡なものよりも幻想的で打ち負かされません。

スティーブンユニバースは、強烈な内部エピソードのペアで、それ自体のバックストーリーをさりげなく粉砕します

スティーブンユニバースは、強烈な内部エピソードのペアで、それ自体のバックストーリーをさりげなく粉砕します

スティーブンユニバースビーチシティのエピソードが実行されるたびに、いくつかのクライマックスイベントが発生し、スティーブンユニバースのより広い神話に対する理解の一部が失われます。これはあなたが期待していたことですか?今日のエピソードは両方とも、容赦なくゆっくりと、シーズンの終盤の主要な部分を設定する決定的な結論に向かって進みます。そして、ロナウドは、静かな納屋が倒れているのを発見した夜中にスティーブンを捕まえるためにやって来ます。月に。

ディズニーワールドの旅行のヒントを教えてください

ディズニーワールドの旅行のヒントを教えてください

「光が触れるものはすべて私たちの王国です。」今週のHackYour Cityでは、1つのテーマパークを取り上げます。

Zendaya Wishes Boyfriend Tom Holland Happy Birthday with Cuddly Photo: He 'Makes Me the Happiest'

Zendaya Wishes Boyfriend Tom Holland Happy Birthday with Cuddly Photo: He 'Makes Me the Happiest'

Zendaya shared a sweet photo in honor of boyfriend Tom Holland's 26th birthday Wednesday

小さな女性:脳卒中を患った後に病院から解放されたアトランタのジューシーな赤ちゃん:「まだ癒し」

小さな女性:脳卒中を患った後に病院から解放されたアトランタのジューシーな赤ちゃん:「まだ癒し」

シーレン「Ms.JuicyBaby」ピアソンは、先月脳卒中で入院した後、「もう一度たくさんのことをする方法を学ばなければならない」ため、言語療法を受けていることを明らかにしました。

エマストーンは彼女のクリフサイドマリブビーチハウスを420万ドルでリストアップしています—中を見てください!

エマストーンは彼女のクリフサイドマリブビーチハウスを420万ドルでリストアップしています—中を見てください!

オスカー受賞者の世紀半ばの家には、3つのベッドルーム、2つのバス、オーシャンフロントの景色があります。

ジーニー・メイ・ジェンキンスは、母乳育児の経験の中で、彼女は「本当に、本当に落ち込んでいる」と言います

ジーニー・メイ・ジェンキンスは、母乳育児の経験の中で、彼女は「本当に、本当に落ち込んでいる」と言います

ジーニー・メイ・ジェンキンスは、生後4か月の娘、モナコに母乳育児をしていると語った。

投資ノート:Bioscout AU$300万シード

投資ノート:Bioscout AU$300万シード

Bioscoutは、農家を運転席に置くという使命を負っています。Artesian(GrainInnovate)やUniseedと並んで、最新のシードラウンドでチームを支援できることをうれしく思います。問題真菌症による重大な作物の損失は、農民にとって試練であることが証明されています。

リトルマーケットリサーチ1| 2022年のクイックグリンプス遠隔医療市場

リトルマーケットリサーチ1| 2022年のクイックグリンプス遠隔医療市場

遠隔医療は、パンデミック後の時代では新しいものではなく、時代遅れの分野でもありません。しかし、業界を詳しく見ると、需要と供給の強力な持続可能性と、米国で絶え間ない革命となる強力な潜在的成長曲線を示しています。

スタートアップ資金調達環境:タイのスタートアップエコシステムの次は何ですか?

スタートアップ資金調達環境:タイのスタートアップエコシステムの次は何ですか?

2021年は、世界的なベンチャーキャピタル(VC)の資金調達にとって記録的な年でした。DealStreetAsiaによると、東南アジアも例外ではなく、この地域では年間で記録的な25の新しいユニコーンが採掘されました。

ムーアの法則を超えて

ムーアの法則を超えて

計算に対する私たちの欲求とムーアの法則が提供できるものとの間には、指数関数的に増大するギャップがあります。私たちの文明は計算に基づいています—建築と想像力の現在の限界を超える技術を見つけなければなりません。

Language