3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

表示されていないシート上でのSortメソッド、並べ替え – Excelマクロ・VBA

2012年1月16日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

エクセルマクロ・VBA達人養成塾 小川です。

キューバ旅行記、その83です。

キューバ屈指の外人向けリゾート地、バラデロにて。
バラデロ4日目のお昼。

いよいよ、マタンザス経由の高速バスで、ハバナに帰ります。

本当は、列車で帰りたかったのですが…。列車が運行していないということで、あえなく断念。
荷物を持って、バスターミナルに向かいます。

エクセルマクロ達人養成塾塾長ブログ-バラデロ、表通りから

バラデロ、表通りから、見えた通りをちらっと撮影。

エクセルマクロ達人養成塾塾長ブログ-お土産屋

お土産屋。

エクセルマクロ達人養成塾塾長ブログ-らしい建物

リゾートらしい建物。

エクセルマクロ達人養成塾塾長ブログ-公民館のような建物

公民館のような建物。

エクセルマクロ達人養成塾塾長ブログ-別の角度から。

公民館のような建物。別の角度から。

エクセルマクロ達人養成塾塾長ブログ-絵を売っているよう。

絵を売っているよう。

エクセルマクロ達人養成塾塾長ブログ-露出の多い女性陣。

露出の多い女性陣。とにかく暑い!

エクセルマクロ達人養成塾塾長ブログ-振り返ってみました。

振り返ってみました。

エクセルマクロ達人養成塾塾長ブログ-バスターミナルはもう近し。

バスターミナルは、左側の木々の向こうです。もうすぐ。

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ-ということで、バスに乗り込み、ハバナへ。

ということで、バスに乗り込み、ハバナへ。

この写真は、、もう、バスの中からの風景。
塾長のキューバ旅行記、最初から読みたい方はこちらから


表示されていないシート上でのSortメソッド、並べ替え – Excelマクロ・VBA

今日は、小ネタ。

表示されていないシート上での並べ替えをするには?という件。
何かというと。

Excel 2003までのバージョンでも動くSortメソッドは、以下のような形になる。

(以下のサンプルは、Excel 2003までのバージョンのエクセルて、自動記録をしながら並べ替えの操作をすると、容易に得られる)

Range(“A2:K18”).Sort Key1:=Range(“C2”), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

SortMethod:=xlPinYin, DataOption1:=xlSortTextAsNumbers

以下、途中改行を取り除き、今回の話に大きく関係しそうにない引数を取り除き、整形してみた。

Range(“A2:K18”).Sort _

Key1:=Range(“C2”), _

Order1:=xlAscending, _

Header:=xlGuess

このとき、「この作業対象のシート以外のシートが表示されていたとしても、並べ替えがされるようにしたい」というニーズがある。

例えば、この並べ替え対象の表は、「Sheet1」というシートにある。

そして、表示されているシートは、「Sheet2」だ。

さて、どうするか。

以下では、エラーが発生して動かない。

Sub sample1()

Worksheets(“Sheet1”).Range(“A2:K18”).Sort _

Key1:=Range(“C2”), _

Order1:=xlAscending, _

Header:=xlGuess

End Sub

以下で、動くようになる。

Sub sample2()

Worksheets(“Sheet1”).Range(“A2:K18”).Sort _

Key1:=Worksheets(“Sheet1”).Range(“C2”), _

&nbs
p;   Order1:=xlAscending, _

Header:=xlGuess

End Sub

よくよく考えれば分かることなのだが。

実は、IBMで社内講師をしていたころも、

その後、IT系のベンチャーで働いていたころも、

そのあとも、かなり長いこと、

こんな簡単な問題とは気がつかず、

そういうことはできないものと思って、あきらめていた。

ある日、ふと、「?」と思ってsample2の方法でやってみたら、

「あっさり動いた。。なんだ」と思ったというわけ ヾ(´ー`)ノ

ちなみに、以下の書き方でも、問題回避できる。

(※並べ替え対象の列のタイトルが”社員区分”だったと仮定)

Sub sample3()

Worksheets(“Sheet1”).Range(“A2:K18″).Sort _

Key1:=”社員区分”, _

Order1:=xlAscending, _

Header:=xlGuess

End Sub

ここで、整理してみる。

以下、Excel 2003までの Sort メソッドについてのヘルプから、引用。

key1 省略可能です。バリアント型 (Variant) の値を使用します。最初の並べ替えフィールドを、テキスト (ピボットテーブル フィールドまたは範囲名) または Range オブジェクト (たとえば、”Dept” または Cells(1, 1)) で指定します。

要は、並べ替えする対象の列を、その列のタイトルとなる文字列か、セルで指定すればよい、ということ。
この解決策に気がつくまでは、以下のような回避策を実施していた。

この問題の解決策としてはもはや不適切…というか、ベストにはほど遠いが、手法自体は応用が効くものとも思うので、ご紹介。

Sub sample4()

Dim w As Worksheet

Set w = ActiveSheet

Worksheets(“Sheet1”).Activate

Range(“A2:K18”).Sort _

Key1:=Range(“C2”), _

Order1:=xlAscending, _

Header:=xlGuess

w.Activate

End Sub

いろいろ参考にしてください。


キーワード

コメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド