[VBA] 엑셀 디아블로2 my log(엑셀), jimmy's log(본문)

 

ItemListv0.7_20160327.xls

 

 

 

 

Sub DeleteEmptyRows()
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
End Sub

Sub DeleteEmptyHeaders()
    'Delete the row if column A is empty
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 2 Step -1 'Exclude the first row from the range
        'If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
        If Application.CountA(Cells(r, 1)) = 0 Then Rows(r).Delete
    Next r
    removeColor
End Sub

Sub sortJefOS()
'
' sortJefOS Macro
'
' Hardcoded for only one worksheet of Jimmy's log(Jef-X: Magic&OS)

    Dim srt As Sort
       
    Set srt = ActiveSheet.Sort
   
    With srt.SortFields
        .Clear
        .Add Key:=Columns("H"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Number of sockets
        .Add Key:=Columns("I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Base
        .Add Key:=Columns("A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Account
        .Add Key:=Columns("B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Character
    End With
   
    'With ActiveWorkbook.Worksheets("Magic&OS").Sort
    With srt
        .SetRange Cells
        .Header = xlYes             'My data has headers
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    DeleteEmptyHeaders
End Sub

Sub removeColor()
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
Sub stripTag()
    'Cells.Select
    'Dim rngSelected As Range
    'Set rngSelected = New Cells.Select
    'Call stripHtml(rngSelected) 'html제거
    Cells.Hyperlinks.Delete 'hyperlink 제거
    ActiveSheet.Pictures.Delete '그림 제거
End Sub
Function stripHtml(cell As Range) As String

Dim RegEx As Object
 Set RegEx = CreateObject("vbscript.regexp")
Dim sInput As String
 Dim sOut As String
 sInput = cell.Text

sInput = Replace(sInput, "\x0D\x0A", Chr(10))
 sInput = Replace(sInput, "\x00", Chr(10))

'replace HTML breaks and end of paragraphs with line breaks
 sInput = Replace(sInput, "</P>", Chr(10) & Chr(10))
 sInput = Replace(sInput, "<BR>", Chr(10))

'replace bullets with dashes
 sInput = Replace(sInput, "<li>", "-")

'add back all of the special characters
 sInput = Replace(sInput, "&ndash;", "?")
sInput = Replace(sInput, "&mdash;", "?")
sInput = Replace(sInput, "&iexcl;", "¡")
sInput = Replace(sInput, "&iquest;", "¿")
sInput = Replace(sInput, "&quot;", "")
sInput = Replace(sInput, "&ldquo;", "“")
sInput = Replace(sInput, "&rdquo;", "”")
sInput = Replace(sInput, "", "‘")
sInput = Replace(sInput, "&lsquo;", "‘")
sInput = Replace(sInput, "&rsquo;", "’")
sInput = Replace(sInput, "&laquo;", "≪")
sInput = Replace(sInput, "&raquo;", "≫")
sInput = Replace(sInput, "&nbsp;", " ")
sInput = Replace(sInput, "&amp;", "&")
sInput = Replace(sInput, "&cent;", "￠")
sInput = Replace(sInput, "&copy;", "ⓒ")
sInput = Replace(sInput, "&divide;", "÷")
sInput = Replace(sInput, "&gt;", ">")
sInput = Replace(sInput, "&lt;", "<")
sInput = Replace(sInput, "&micro;", "μ")
sInput = Replace(sInput, "&middot;", "·")
sInput = Replace(sInput, "&para;", "¶")
sInput = Replace(sInput, "&plusmn;", "±")
sInput = Replace(sInput, "&euro;", "€")
sInput = Replace(sInput, "&pound;", "￡")
sInput = Replace(sInput, "&reg;", "®")
sInput = Replace(sInput, "&sect;", "§")
sInput = Replace(sInput, "&trade;", "™")
sInput = Replace(sInput, "&yen;", "￥")
sInput = Replace(sInput, "&aacute;", "a")
sInput = Replace(sInput, "&Aacute;", "A")
sInput = Replace(sInput, "&agrave;", "a")
sInput = Replace(sInput, "&Agrave;", "A")
sInput = Replace(sInput, "&acirc;", "a")
sInput = Replace(sInput, "&Acirc;", "A")
sInput = Replace(sInput, "&aring;", "a")
sInput = Replace(sInput, "&Aring;", "A")
sInput = Replace(sInput, "&atilde;", "a")
sInput = Replace(sInput, "&Atilde;", "A")
sInput = Replace(sInput, "&auml;", "a")
sInput = Replace(sInput, "&Auml;", "A")
sInput = Replace(sInput, "&aelig;", "æ")
sInput = Replace(sInput, "&AElig;", "Æ")
sInput = Replace(sInput, "&ccedil;", "c")
sInput = Replace(sInput, "&Ccedil;", "C")
sInput = Replace(sInput, "&eacute;", "e")
sInput = Replace(sInput, "&Eacute;", "E")
sInput = Replace(sInput, "&egrave;", "e")
sInput = Replace(sInput, "&Egrave;", "E")
sInput = Replace(sInput, "&ecirc;", "e")
sInput = Replace(sInput, "&Ecirc;", "E")
sInput = Replace(sInput, "&euml;", "e")
sInput = Replace(sInput, "&Euml;", "E")
sInput = Replace(sInput, "&iacute;", "i")
sInput = Replace(sInput, "&Iacute;", "I")
sInput = Replace(sInput, "&igrave;", "i")
sInput = Replace(sInput, "&Igrave;", "I")
sInput = Replace(sInput, "&icirc;", "i")
sInput = Replace(sInput, "&Icirc;", "I")
sInput = Replace(sInput, "&iuml;", "i")
sInput = Replace(sInput, "&Iuml;", "I")
sInput = Replace(sInput, "&ntilde;", "n")
sInput = Replace(sInput, "&Ntilde;", "N")
sInput = Replace(sInput, "&oacute;", "o")
sInput = Replace(sInput, "&Oacute;", "O")
sInput = Replace(sInput, "&ograve;", "o")
sInput = Replace(sInput, "&Ograve;", "O")
sInput = Replace(sInput, "&ocirc;", "o")
sInput = Replace(sInput, "&Ocirc;", "O")
sInput = Replace(sInput, "&oslash;", "ø")
sInput = Replace(sInput, "&Oslash;", "Ø")
sInput = Replace(sInput, "&otilde;", "o")
sInput = Replace(sInput, "&Otilde;", "O")
sInput = Replace(sInput, "&ouml;", "o")
sInput = Replace(sInput, "&Ouml;", "O")
sInput = Replace(sInput, "&szlig;", "ß")
sInput = Replace(sInput, "&uacute;", "u")
sInput = Replace(sInput, "&Uacute;", "U")
sInput = Replace(sInput, "&ugrave;", "u")
sInput = Replace(sInput, "&Ugrave;", "U")
sInput = Replace(sInput, "&ucirc;", "u")
sInput = Replace(sInput, "&Ucirc;", "U")
sInput = Replace(sInput, "&uuml;", "u")
sInput = Replace(sInput, "&Uuml;", "U")
sInput = Replace(sInput, "&yuml;", "y")
sInput = Replace(sInput, "", "´")
sInput = Replace(sInput, "", "`")

'replace all the remaining HTML Tags
 With RegEx
 .Global = True
 .IgnoreCase = True
 .MultiLine = True
 .Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.

End With
 sOut = RegEx.Replace(sInput, "")
stripHtml = sOut
 Set RegEx = Nothing

End Function