こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルVBAで、列の並び替えをしたいです。

いつも大変お世話になっております。
初心者につき、ご教示いただけますと幸いです。

A列~BQ列までを使用したデータが
日々更新されるのですが、
デイリーでお客様へ提出する資料で必要な項目は限られており
また、配列も異なる(前後している)仕様となっているため、
日々、手作業で必要な配列の抜き出しと列の配置替えをしています。

これをVBA(マクロ)で設定しておけば、
日々の作業が軽減できるかと考えておりますが、
記憶マクロで作ってはみたものの
知識不足のため、
日々更新され、範囲(行)が異なってしまうデータに対し
そこにどう対応して良いかが分かりません。

お教えいただけますと幸いです。

※A列は項番・BQは必須項目のため、Nullはありませんが、
他の項目にはNullもあります。

よろしくお願い致します。

投稿日時 - 2018-11-01 09:59:09

QNo.9553370

困ってます

質問者が選んだベストアンサー

複写先は同じブックの別シート、
行の並べ替えや削除などは行わず、
単に、指定した列を指定した順番にコピーするだけなら
次のようなコードはいかがでしょうか。

求める仕様が↑と異なるのであればコメントしてください。

Option Explicit

Sub ColCopy()

 Dim MyCols As Variant
 Dim iSh As Worksheet
 Dim oSh As Worksheet
 Dim ColCounter As Long
 
 Const iShName = "Sheet1"  '複写元シート名
 Const oShName = "Sheet2"  '複写先シート名
 Const ColCount = 4     '複写する列数
 MyCols = Array(1, 7, 2, 9) '複写する列番号とその順番
 
 With ThisWorkbook
  Set iSh = .Sheets(iShName)
  Set oSh = .Sheets(oShName)
 End With
 
 For ColCounter = 1 To ColCount
  iSh.Columns(MyCols(ColCounter - 1)).Copy oSh.Columns(ColCounter)
 Next ColCounter

End Sub

投稿日時 - 2018-11-01 12:58:31

補足

VBAのコード自体がよくわからないのですが、
例えば、A~BQ列までのデータを、A・T・V・G・BQ・・・といったように
列の配置が異なるので、入れ変えたいのですが、
ご教示いただいたコードで、それは可能なのでしょうか?
可能な場合、どのへんを変更すると、配列を操作できますでしょうか。

ご面倒おかけしますが、お手ほどきのほど、よろしくお願い致します。

投稿日時 - 2018-11-01 15:27:50

お礼

うまく仕様に合わせることができました。
とても助かりました。
ありがとうございます!

投稿日時 - 2018-11-01 19:17:26

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

>A列からBQ列を1行目を基準に降順で並び替え
「昇順で並び替え」の書き間違いです。m(__)m

投稿日時 - 2018-11-01 17:13:23

ANo.6

>列の並び替えをしたいです。
できますよ
Sub 列の並び替え()
  Dim LR As Long
  '作業行を挿入 
  Rows(1).Insert
  '一行目の作業行に並び順を設定
  Range("A1").Value = 1
  Range("T1").Value = 2
  Range("V1").Value = 3
  Range("G1").Value = 4
  Range("BQ1").Value = 5
  '最終行を求める
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  'A列からBQ列を1行目を基準に降順で並び替え
  Range("A1:BQ" & LR).Sort _
  Key1:=Range("A1"), Order1:=xlAscending, _
  Header:=xlNo, Orientation:=xlLeftToRight
  ActiveSheet.Sort.SortFields.Clear
  '一行目に挿入した作業行を削除
  Rows(1).Delete
End Sub

投稿日時 - 2018-11-01 17:10:20

お礼

ご教示、ありがとうございます!
とても勉強になりました!

投稿日時 - 2018-11-01 19:18:22

ANo.5

>例えば、A~BQ列までのデータを、A・T・V・G・BQ・・・といったように
>列の配置が異なるので、入れ変えたいのですが、

MyCols = Array(1, 7, 2, 9) '複写する列番号とその順番
このコードが複写する列と複写する順番です。
この場合、
複写先の1列目には、複写元の1列目が
複写先の2列目には、複写元の7列目が
複写先の3列目には、複写元の2列目が
複写先の4列目には、複写元の9列目が複写されます。

>A・T・V・G・BQ・・・ ならば
MyCols = Array(1, 20, 22, 7, 69,....) 
といった記述になります。

投稿日時 - 2018-11-01 15:59:46

お礼

ありがとうございました!!

投稿日時 - 2018-11-01 19:18:40

ANo.4

すでにご指摘の、原データは保存し、コピーデータで処理するのは、必須でしょう。
ーー
しかし、
この質問者の、質問の説明程度では、したいこと、や問題点の詳細が、読者には、判らないと思うので、処理の要点の説明や、コード例が書けないと思う。
ーー
データ模擬実例(サンプル)を挙げて(10レコードぐらい最低必要か)、そのデータの場合、最終完成形ではどうなってほしいのか、文章で説明すべきでしょう。
 普通は、仕事の処理に当たって、仕様書というようなものを作ると思うが、文章化することによって、考えや、すべきことが明確になるものと思うよ。
ーー
質問者は毎日仕事で苦労しているから、状況は頭に入っているが、内容に関しては、(VBAコード作成のスキルはあっても)読者は、何も知らないのだよ。
多分ポイントは1,2点にしか過ぎない、と思うが、そのポイントが説明できないのが、素人だよね。
ーー
質問表現の中の、例えば>日々更新され、範囲(行)が異なってしまうデータに対し・・
なぜ範囲を考える必要があるのか?今あるデータを対象に、ある列や複数列で、データをソートすれば済まない状況は何?
修正ソートキーというものを自作しないとならない場合はあるが、そんなけーしかな。

投稿日時 - 2018-11-01 15:23:22

お礼

列の配列を並び替える・・ため、データソートとは異なる質問になります。
→データソートは、行の並び替えですよね?(こちらの知識不足でしたら申し訳ありません!)
ご指摘の「説明がわかりにく」点も、大変申し訳ありません。
確かにその通りだと反省致します。
ご不快と感じるようでしたら、スルーいただいて構いませんので、お許しください。
申し訳ございません。

投稿日時 - 2018-11-01 15:36:46

ANo.2

>直で並び替えができると最善かと考えていたんですが・・・

「最善」に思える手段が、実は「最悪」なのです。

「並び替えて提出」の場合、提出した後に「元に戻す処理」が必要になります。

「元に戻す処理」をしないと「2重に並び替えが起きて、訳が判らない状態」になります。

また「並び替える際に、どこかの列を削除」したりすれば「元に戻す事は不可能」になります。

なので、もし並び替えを行なうとしても「元データを別シートにコピーして、別シートを並び替える」と言う処理が必要です。

間違って「元に戻すのを忘れたまま、並び替えをする」と、データがグチャグチャになりますし、間違って「原本」を並び替えしてしまうと、取り返しが付かなくなります。

なので「コピーして、複製したのを並び替え」をする必要がありますが「複製したのを並び替え」するのであれば「別シートに参照式を書く」のと何も変わりませんし、並び替えする手間が増えるだけです。

>それも作ってはみましたが、
>(式が入ったままをお客様へ提出はできないので)

であれば「参照式を使った提出用シート」全体を「コピー」して「形式を指定して貼り付け」の「値」で、シートごと別の「提出専用ブック」に「貼り付け」して、その「提出専用ブック」を客先に提出しましょう。

つまり、原本ブックに「元データ」「参照式を使った提出用原本シート」の2つのシートを作り、それとは別の提出用ブックに「値で貼り付けした提出用本番シート」を作る訳です。

「並び替える」は、確かに「目的地までの最短距離」に見えますが、こういう場合は「急がば回れ」した方が「処理は簡単」になり、事故も減ります。

投稿日時 - 2018-11-01 12:46:11

お礼

細かなご指摘、ありがとうございます。
確かに、直操作は危険なことでした。うかつな考えだったと反省です。
直ではなく、シートにコピーしたものを直で動かす・・・方法だと、ご指南いただいた関数式を入れる・・・と、あまり変わらないのでしょうか?
なにせ、いつ時点でどこまでのデータ量に増えるかがわからないため、
式を設定しておく・・という手段だと、足りなくなった時に「対応忘れ」る可能性があるもので・・・。
(なぜかうちには、スキルの低いお爺様もいて、その方が処理をする機会もあるため、なるべく手作業をはぶきたい!というのが正直です。)

投稿日時 - 2018-11-01 15:33:13

ANo.1

>日々、手作業で必要な配列の抜き出しと列の配置替えをしています。

そういう場合は「元データが入っているシート」と別に「提出用シート」を作ります。

そして、提出用シートには「元データの入っているシートを参照する式」を使って「提出すべきデータのみ、並びを変えて表示」するのです。

例えば、元データがSheet1、提出用シートがSheet2だとして

元データのA列を、提出用シートのA列に
元データのB列を、提出用シートのB列に
元データのC列を、提出用シートのD列に
元データのD列を、提出用シートのE列に
元データのF列を、提出用シートのC列に
元データのG列を、提出用シートのF列に

というように表示したいなら

提出用シートのA1に「=Sheet1!A1」と入力して下方向にコピー
提出用シートのB1に「=Sheet1!B1」と入力して下方向にコピー
提出用シートのD1に「=Sheet1!C1」と入力して下方向にコピー
提出用シートのE1に「=Sheet1!D1」と入力して下方向にコピー
提出用シートのC1に「=Sheet1!F1」と入力して下方向にコピー
提出用シートのF1に「=Sheet1!G1」と入力して下方向にコピー

と言う作業をすれは、列の順番・並びが変わった「提出用シート」が簡単に出来上がります。

そして、元データを更新すれば、自動的に、提出用シートの値も更新されます。

この時「元データは変にいじらない」のがコツです。

元データをいじると、提出を行なった後に、毎回「元の順番に戻す処理」が必要になってしまいます。

そして、この様に「元データを参照して、提出用シートを別に作る」ようにすると、2回目以降は「元データを更新するだけ」で、何もする必要はありません。元データが更新されると提出用シートも自動更新されます。

投稿日時 - 2018-11-01 10:30:21

お礼

改善案のご教示、ありがとうございます。

リンク式を入れた提出用のフォーマットを作っておく・・という方法ですよね?
それも作ってはみましたが、
(式が入ったままをお客様へ提出はできないので)
お客様提出用を別で作成しなくてはいけなくなることと、
人によっては、コピー&ペースト(値貼付)がうまくできない人もいるため
直で並び替えができると最善かと考えていたんですが・・・

投稿日時 - 2018-11-01 11:54:10

あなたにオススメの質問