[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セルのデータをずらしたい』(nobuyuki)
下のように例えば3x4のデータを5x3のデータに一括でずらすことはできるのでしょうか?
1 2 3
4 5 6
7 8 9
10 11 12
↓
1 2 3 4 5
6 7 8 9 10
11 12 なし なし なし
よろしくお願いします。
< 使用 Excel:Office365、使用 OS:Windows10 >
Sub test() Const XMAX = 5 Dim vw() As Variant Dim i As Long Dim iy As Long
With Range("A1").CurrentRegion iy = (.Count - 1) \ XMAX ReDim vw(iy, XMAX - 1) For i = 1 To .Count vw((i - 1) \ XMAX, (i - 1) Mod XMAX) = .Item(i).Value Next i .ClearContents End With
Range("A1").Resize(iy + 1, XMAX).Value = vw End Sub (???) 2019/06/19(水) 15:25
私もちょっろっと参加(^^;
Option Explicit Sub てすと() Dim r As Range Dim v As Variant Dim i As Long Dim j As Long Dim k As Long With Sheets("Sheet1") Set r = .Range("A1").CurrentRegion End With ReDim v(1 To 3, 1 To 5) For i = LBound(v, 1) To UBound(v, 1) For j = LBound(v, 2) To UBound(v, 2) k = k + 1 v(i, j) = r(k).Value Next Next With Sheets("Sheet2") .Cells.Clear .Range("A1").Resize(UBound(v, 1), UBound(v, 2)).Value = v End With Set r = Nothing Erase v End Sub すみません。.Value は、省略してはいけませんね。。。 (SoulMan) 2019/06/19(水) 20:07
Option Explicit
Sub test() Dim r1 As Range Dim r2 As Range Dim v, e Dim n As Long
Set r1 = Worksheets("sheet1").Range("A1").CurrentRegion Set r2 = r1.Resize(r1.Count, 5) '★5列に並べ替え v = WorksheetFunction.Transpose(r1.Value) r1.ClearContents
For Each e In v n = n + 1 r2(n).Value = e Next
End Sub
(マナ) 2019/06/19(水) 22:40
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.