セルの頭に式を入れていて、それを最終行までオートフィルしたいのですが、うまくいきません。
セルは全て2行ずつ結合しています。
その状態で
With Cells(Rows.Count, 5).End(xlUp)
.Offset(1, 1) = "最終判定"
.Offset(1, 2) = "最終判定"
.Offset(1, 3) = "最終判定"
.Offset(1, 4) = "最終判定"
.Offset(1, 5) = "最終判定"
.Offset(1, 6) = "最終判定"
.Offset(1, 7) = "最終判定"
.Offset(1, 8) = "最終判定"
.Offset(1, 9) = "最終判定"
.Offset(1, 10) = "最終判定"
.Offset(1, 11) = "最終判定"
.Offset(1, 12) = "最終判定"
Range("F5:F6").AutoFill Destination:=Range(Range("F5:Q6"), Selection.End(xlDown)), Type:=xlFillSeries
というマクロを使用していますが(色々弄くり回していたので原型と少々変わってるかもしれません)、セルが結合している為出来ませんなどのエラーが出ます。
そこで回避手段として下記のマクロを組んでみました。
Public Sub testcode()
'ダミーデータ
'5000行まで結合する為にダミーの判定用データを作成する(5000行までまずいかない)
With Cells(5000, 5)
.Offset(1, 1) = "最終行判定用"
.Offset(1, 2) = "最終行判定用"
.Offset(1, 3) = "最終行判定用"
.Offset(1, 4) = "最終行判定用"
.Offset(1, 5) = "最終行判定用"
.Offset(1, 6) = "最終行判定用"
.Offset(1, 7) = "最終行判定用"
.Offset(1, 8) = "最終行判定用"
.Offset(1, 9) = "最終行判定用"
.Offset(1, 10) = "最終行判定用"
.Offset(1, 11) = "最終行判定用"
.Offset(1, 12) = "最終行判定用"
End With
'月の下のセルを結合する
For m = 6 To 17
Set c = Cells(5, m)
Cells(5, m).Activate
Do Until c.Value <> "" Or c.Offset(1).Value <> ""
c.Resize(2).Merge
Set c = c.Offset(1)
Loop
Next m
Range("F5") = "=IF~省略"
Range("F5:F6").AutoFill Destination:=Range("F5:Q6"), Type:=xlFillSeries
'ダミーの5000行までオートフィルする
Range("F5:Q6").AutoFill Destination:=Range("F5:Q5000"), Type:=xlFillSeries
With Range("F5:Q5000")
'選択範囲内で中央配置
.HorizontalAlignment = xlHAlignCenterAcrossSelection
.Copy
End With
Range("F5").PasteSpecial Paste:=xlPasteValues
'コピーの解除
Application.CutCopyMode = False
'ダミーデータの選択
Range(Cells(Rows.Count, 5).End(xlUp).Offset(1, 1), Cells(Rows.Count, 5).End(xlUp).Offset(1, 12)).Select
'削除
Range(Cells(Rows.Count, 5).End(xlUp).Offset(1, 1), Cells(5001, 17)).Delete
End Sub
1.まず、F~Q列の5001行に文字を入れる
2.5000行まで2行ずつセルを結合する、F~Q列全て行う
3.F5にオートフィルしたい式を入れる
4.F5~Q6(ここも2行ずつ結合してあります)までオートフィルをする
5.F5~Q6に入っている式を下方向へオートフィルする
6.本来なら不要のダミーデータを選択し、削除する
A~E列までは別にデータが入っていて、F~Q列はA~E列の最終行に合わせてオートフィルさせる、という目的で上記マクロを作成し使用しております。
一応これで動くのですが問題点があります。
1.5000行まで無駄なデータを入れているため結合処理が遅い(普段は300~1500程度だが中途半端に設定すると最終行が取得できない可能性があるため)
2.同じく無駄なデータの削除処理が遅い
以上2点が問題点となっております。
上記処理をなくしたいのですが、2行ずつの結合したセルに対して、位置が不明確な場合、オートフィルを飛ばすのは難しいでしょうか?
よいマクロが有れば教えてください。
No.5ベストアンサー
- 回答日時:
こんな感じでどうでしょうか。
Dim rw As Long
rw = Cells(Rows.Count, 5).End(xlUp).Row
Application.ScreenUpdating = False
With Range("F5:F6")
.Merge
.Formula = "=Sheet2!A1"
End With
Range("F5:F6").AutoFill Destination:=Range("F5:Q6"), Type:=xlFillSeries
Range("F5:Q6").AutoFill Destination:=Range("F5:Q" & rw), Type:=xlFillSeries
Application.ScreenUpdating = True
No.4
- 回答日時:
読み返して、「あ~、そうか。
」とようやく納得。> 1.まず、F~Q列の5001行に文字を入れる
> 2.5000行まで2行ずつセルを結合する、F~Q列全て行う
> 3.F5にオートフィルしたい式を入れる
> 4.F5~Q6(ここも2行ずつ結合してあります)までオートフィルをする
> 5.F5~Q6に入っている式を下方向へオートフィルする
要するに、結合してからフィルしようとしているのですね。
1.F5セルにオートフィルしたい式を入力
2.F5・F6セルを結合
3.Q列まで(結合されていない)列方向にフィル
4.最終行まで(結合されていない)行方向にフィル
の順番のほうが良いですよ。
マクロを使わずに、エクセルで直接フィルする時も
途中に結合セルがあると失敗することがありますよね。
マクロでもそれと同様ですから。
説明下手で誤解を与えてしまい申し訳ないです
結合してからフィルを行おうとしていました
手動でオートフィルを行うと問題なく動いていたもので・・・
しかし処理の順番が悪いと言うのもあったのですね
以後の参考にします
貴重なお時間を煩わせて申し訳ありません、そしてありがとうございました
No.3
- 回答日時:
うまく伝わらなかったようなので。
少々長くなりますがご容赦ください。
「オートフィル」がお好みのようですね。
じゃあ、オートフィルでいきましょう。
この場合に問題になるのは、A~E列が「どのように入力されているか、ですね。
5・6行目についてだけ、まずは考えて見ます。
考えられるパターンは3つ
・5行目だけに値が入っている
・6行目だけに値が入っている
・どちらも値が入っている
と言うパターンですね。
コレ以降の行に拡張すると、
・奇数行だけに値が
・偶数行だけに値が
・どちらにも値が
と言う3パターンに加えて
・混在している
と言う4つ目のパターンも考えられます。
また例外として、
・どちらも空白である
と言う選択肢も発生するかもしれません。
ただし、これはうまくないのは言うまでもありませんね。
もし実際の最終行がこの状態(奇数偶数どちらも空白)なのであれば、
データまたは最終行判定条件を見直すことを強くオススメします。
続いて最終行を判定する条件。
奇数行(5行目)から開始して、2行ごとに結合していくのですから、
「処理後の最終行は必ず偶数行である」と言えます。
2行を結合したセルを下(行)方向にフィルしていくわけです。
処理する行数が奇数だと、最終的に「く都合するべきセルが範囲に無い」ため、
エクセルはエラーを返すしかなくなるわけです。
つまり、xlupなどで見つけた最終行が奇数なら(処理範囲の行数が奇数なら)、
次の行(偶数行)までの処理(処理行数が偶数)であると置き換える必要があります。
見つけた最終行が偶数行(処理行数が偶数)であれば、そのまま処理を流してOKです。
これが解れば、
> 中途半端に設定すると最終行が取得できない可能性
は無いことが解ります。
つまり、奇数行で終わる場合は「次の行まで」
偶数行で終わるなら「その行まで」と言う終了条件をつければ良いのです。
見た感じ、E列を基準に「最終行」を拾っているようですので、
とりあえず、それを採用して書いてみます。
Sub sample1()
Dim MaxRow As Long
MaxRow = Cells(Rows.Count, 5).End(xlUp) ' 実データE列の最終行取得
If MaxRow Mod 2 = 1 Then MaxRow = MaxRow + 1 '最終行が奇数なら次の行までに拡張
With Range("F5") ' F5セルに対して
.Value = "=SUM($E5:E5)" ' 式を文字列として入力(仮)
.Resize(2, 1).Merge ' 下のセル(F6セル)と結合
End With
' 横方向にQ列までフィル
Range("F5:F6").AutoFill Destination:=Range("F5:Q6"), Type:=xlFillDefault
' 縦方向にMaxRow行目までフィル
Range("F5:Q6").AutoFill Destination:=Range("F5:Q" & MaxRow), Type:=xlFillDefault
End Sub
入力したい式がわかりませんので適当な式を文字列で入力していますが、
ここはもちろんお使いの式に直してくださいませ。
文字列を入力する場合は
.Value = "文字列"
としてやればOKです。
図は"文字列"を入力して走らせた結果です。
実際のデータの最終行が奇数行(21行目)ですので、
その下の行(22行目)までを範囲として判定しています。
見やすいかどうかはわかりませんが、罫線を入れ、
結合された行を1行おきに色をつけています。
上記の
・奇数行だけに値が(11・12行目など)
・偶数行だけに値が(15・16行目)
・どちらにも値が(5・6行目など)
つまり
・混在している
と言う入力条件を全て網羅していると言うこと、
及び、データの途中であれば
・どちらも空白(7・8行目)
にも対応できているのが確認いただけると思います。
(重ねますが、最終行が「どちらも空白」には対応できません。)
ちなみにコレで5000行でも1秒かかりません。
(パソコンのスペックにもよるでしょうが、気になる遅さではないはずです。)
前の回答で言いたかったことは
「とりあえずいろいろな可能性を考えて、実際に動かしてみてください。」
と言うことです。
オートフィルにこだわらず、1セルずつ結合していく手段もありますよ、と言うことですね。
その他にも、
横方向にフィルが出来たのであれば、それをコピーして1行おきに貼り付ける手段もあるわけです。
ひとまず、上記コードを一度走らせてみてください。
で、ご自身のコードと見比べて補完なさってください。
No.2
- 回答日時:
提示例に則ってやるとすれば
With Cells(Rows.Count, 5).End(xlUp)
Range(.Offset(1, 1), .Offset(1, 12)).value = "最終判定"
End With
With Range("F5:Q6")
.AutoFill Destination:=Range(.Cells, .Cells.End(xlDown).Offset(-1)), Type:=xlFillSeries
End With
No.1
- 回答日時:
長いので詳しく読んでいませんが・・
要するに
・1行おきにデータ(式など)を入力
・2行1列ずつ結合
したいのでしょうか。
何かの参考になるかもしれませんので、
新規ブックで下記を走らせてみてください。
Sub sample()
For i = 1 To 5000 Step 2
Cells(i, 1) = "あ"
Cells(i, 1).Resize(2, 1).Merge
Next
End Sub
まぁつまり、入力と結合を一気にやってみようと言うだけのことです。
さすがに5000行をこなそうと思ったら時間はかかりますけどね。
5000行まで入力すると言うのは、回避手段なので本来は回避したいんです
2行ずつ結合した、E~QまでのEnd(xlDown)で取得した最終行まで一気にオートフィルをしたいのです・・・。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) VBAのoffsetの動き方について教えてください 3 2022/11/25 23:36
- Visual Basic(VBA) 集計シートA列のコードと一致する右に並んだシート名(コード)の3行目から10行目をコピーして貼り付け 4 2022/08/18 15:24
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) 3つの条件を指定してVBAで行を削除したい 条件1:分類1が重複 条件2:分類2が重複 条件3:個数 6 2022/06/24 11:07
- Excel(エクセル) EXCEL マクロで行を挿入して貼り付けようとするとエラーになる。 2 2022/05/24 09:43
- Excel(エクセル) vba 転記するときの最終行について 2 2022/09/03 09:31
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) 【VBA】A列にある連続したデータの1番下に文字列を入力したい 1 2023/01/28 04:40
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
【excelVBA】Findメソッドで検索対象を複数列
Excel(エクセル)
-
エクセル マクロ オートフィルの終点の指定について
Excel(エクセル)
-
結合されたセルに連番を振るには?
Excel(エクセル)
-
-
4
ユーザーフォームを表示中にシートの操作をさせるには
Excel(エクセル)
-
5
VBAでメモ帳にコピペをしたいのですが…
PowerPoint(パワーポイント)
-
6
VBAでの結合セルのコピー&ペースト
Excel(エクセル)
-
7
targetをA列のセルに限定するには?
Visual Basic(VBA)
-
8
Excel マクロ VBA プロシージャが大きすぎます のエラー対処方法
Visual Basic(VBA)
-
9
EXCELで、結合されたセルに連続して他のシートからセルの引用をしたい
その他(Microsoft Office)
-
10
結合したセルのオートフィルターをVBAで。
Excel(エクセル)
-
11
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
12
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
13
数式の結果が空白の時の空白扱い
Excel(エクセル)
-
14
B列の最終行までA列をオートフィル
Visual Basic(VBA)
-
15
VBA初心者です。結合セルを保持したまま挿入する方法を教えてほしいです。
Visual Basic(VBA)
-
16
VBA シートをコピーする際に Copyメソッドは失敗しましたのエラーが出てしまいます
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【Excel関数】UNIQUE関数で"0"...
-
エクセルで特定の文字列が入っ...
-
[EXCEL]ボタン押す→時刻が表に...
-
エクセル マクロ オートフィ...
-
アクティブになっている行をマ...
-
エクセル 上下で列幅を変えるには
-
電話番号の入力方式が違うデー...
-
Excel 時刻の並び替え
-
Excel グラフのプロットからデ...
-
サイズの違うセル 並べ変え
-
セルの色によって条件文をつけ...
-
エクセル 時間の表示形式AM/PM...
-
VBAで色の付いているセルの行削除
-
Excelで結合されたセルの間に列...
-
excelのデータで色つき行の抽出...
-
エクセルVBA 最終行を選んで並...
-
Excel ウインドウ枠の固定をす...
-
エクセルのマクロで意図しない...
-
EXCELで最後の行を固定
-
特定の文字がある行以外を削除...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで特定の文字列が入っ...
-
エクセル マクロ オートフィ...
-
【Excel関数】UNIQUE関数で"0"...
-
[EXCEL]ボタン押す→時刻が表に...
-
結合されたセルをプルダウンの...
-
エクセル マクロで数値が変っ...
-
Excel グラフのプロットからデ...
-
AのセルとB行を比較して、一致...
-
エクセル 上下で列幅を変えるには
-
Excel ウインドウ枠の固定をす...
-
特定の文字がある行以外を削除...
-
excelのデータで色つき行の抽出...
-
エクセル2016で時間を入力して...
-
excel 小さすぎて見えないセル...
-
EXCELで最後の行を固定
-
エクセルVBA 最終行を選んで並...
-
VBAで色の付いているセルの行削除
-
エクセルマクロで偶数行(又は...
-
エクセルのセルに指定画像(.jpg...
-
罫線の斜線を自動で引くマクロ
おすすめ情報