Excel小ネタ1
B列に値が入っていない行をまとめて削除する
Sub delete_row() Dim i As Integer For i = 4 To 200 'とりあえず200行までループ If Cells(i, 2).Value = "" Then Rows(i & ":" & i).Delete End If Next i End Sub
セルにセットされているハイパーリンクを隣のセルに書き出す。
Public Sub getUrl() Dim h As Hyperlink Dim address As String Dim subAddress As String For Each h In ActiveSheet.Hyperlinks address = h.Address subAddress = h.SubAddress If subAddress <> "" Then address = address & "#" & subAddress End If h.Range.Offset(0, 1) = address Next End Sub
セルの番号から列名を取得
i = ActiveCell.Column If i >= 1 And i <= 256 Then t2 = Mid(Cells(1, i).Address, 2, InStr(Cells(1, i).Address, "1") - 3) & "4" Else t2 = "" End If
ハイパーリンクの解除
Sub deleteLink() Dim link As Hyperlink For Each link In ActiveSheet.Hyperlinks link.Delete Next link End Sub