Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2013)
オートフィルタ機能を隣のセルと同じところで止める
投稿日時: 19/09/26 19:39:19
投稿者: ExcelTravel

初めまして
初心者の域を出ることができず、VBAはマクロを変えて使用しています。
データの量が多くて普通に計算式を入れているとフリーズしてしまうので、VBAを使っています。(ただセキュリティが厳しくVBAは保存できませんので毎回ノートパッドからコピーして使用します)
二つに分けているのは、一緒にするとパソコンに負担がかかりすぎて現在は計算に時間がかかるためです。
普通、セルの右下をダブルクリックするとオートフィルタ機能で隣の列と同じところまでそのセルの内容(または連続した数値)がコピーされますよね?でも、下記の式では列の一番下まで計算式が入ってしまい計算が終わって保存されるまでに数分〜30分ほどかかってしまいます。手ですると一時間ほどかかることもあります。シートによってデータの量は異なりますが、多いときは300,000行ほどありますが、Hは多くても7000行ほどに減ります。
オートフィルタ機能で隣の列と同じところで止める、というのはVBAでは可能でしょうか?
似た投稿を探したのですが、探し方が悪いのか、基本的な考え方が間違っているのか、見つかりませんでした。
宜しくお願い致します。
 
Option Explicit
Sub Macro2()
'
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.Goto Reference:="R2C8"
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("H1").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:= _
        xlYes
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R2C2:R22688C2,RC[-1])"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2" & Cells.Rows.Count).End(xlUp).Row
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Save
End Sub
 
Sub Macro3()
    Range("I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add _
            Key:=Range(Selection, Selection.End(xlDown)), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
    Range("I1:H1").Select
     Range(Selection, Selection.End(xlDown)).Select
    With ActiveSheet.Sort
        .SetRange Range(Selection, Selection.End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("I:I").Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    ActiveWorkbook.Save
End Sub

回答
投稿日時: 19/09/26 20:44:41
投稿者: WinArrow
投稿者のウェブサイトに移動

最初に
>オートフィルタ機能
は間違い
オートフィル機能
です。
 
それは、それとして
コードを2ヶ所変更してみてください
 
1ヶ所目

引用:
Range("I2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C2:R22688C2,RC[-1])"


 Range("I2").Formula = "=COUNTIF($B$2:$B$" & Range("B" & Rows.Count).End(xlUp).Row & ",H2)"
 
2か所目
引用:

 Range("I2").Select
 Selection.AutoFill Destination:=Range("I2" & Cells.Rows.Count).End(xlUp).Row


 Range("I2").AutoFill Destination:=Range("I2:I" & Range("H" & Rows.Count).End(xlUp).Row)
 
2か所とも、2行のコードを1行で記述しています。
※Selectを使わないようにしましょう。

投稿日時: 19/09/26 21:21:47
投稿者: ExcelTravel

”オートフィル”だから検索できなかったんですね。
 
明日職場でやってみますが、今少し試してみたらできました!
Select を使わないほうがいいんですね〜。
一番わかりやすいというか、そのやり方でしかセルを指定できないのでずっと使っていました。
 
ありがとうございました。