ExcelVBA メモ

Sub nullセルに1つ上の値を入力()

selectarea = Selection.Address

rowno = Selection.Row
colno = Selection.Column

Set area = Selection

toprow = area.Cells(1).Row '上行番号
startcolumn = area.Cells(1).Column '選択開始列番号

max_row = area.Cells(area.Count).Row '一番下の行番号

Range("a1").Value = max_row

'選択してあるセルを順次アクセス

For i = 2 To area.Count
If area.Cells(i) = "" Then
area.Cells(i).Value = area.Cells(i - 1).Value
End If

Next i
End Sub

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

Sub その他()
Dim rowscount As Integer

'A_C列に挿入
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Insert Shift:=xlToRight

Range("C2").Value = "イベントタイムH"
'№をふる
Range("A2").Value = "№"
rowscount = Range("B65535").End(xlUp).Row
rowscount = rowscount - 2 'タイトル行分マイナス

Range("A3").Value = "1"
Range("A3").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=rowscount, Trend:=False

'時間を取り出し
Range("C3").Select
ActiveCell.FormulaR1C1 = "=HOUR(RC[-1])"
Selection.AutoFill Destination:=Range(Cells(3, 3), Cells(rowscount + 2, 3))

'コピー値貼り付け。表示形式をG/標準
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormatLocal = "G/標準"

'連番でない場合行挿入
For i = Cells(3, 5) To area.Count

If Cells(3, 5).valu = (Cells(3 + 1, 5).valu) - 1 Then
Else
Rows("3+1:3+1").Select
Selection.Insert Shift:=xlDown
End If

If area.Cells(i) = "" Then
area.Cells(i).Value = area.Cells(i - 1).Value
End If

Next i

End Sub

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

Sub 整表()
Dim thismacrobook
Dim activebook

thismacrobook = Workbook.Name
activebook = activebook.Name
Row = ActiveSheet.Columns

coll ID並び変え
coll 関数を入力

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

■columns("B").select　　'単一列（B）列を選択（B:D）とすると複数列
■rows(2).select　　　　'単一行を選択
■worksheets("sheet1").actibate
　range("A"& rows.count).end(xlup).select 'A列の最終行から終端セルを選択