【VBA】【一天的心血，收藏一下】一键生成报表

平淡如茶

于 2018-10-31 10:18:38 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

分类专栏： VBA 文章标签： vba vlookup vba数组单元格转一维数组删除空行

本文链接：https://blog.youkuaiyun.com/qq_27469815/article/details/83576185

通过VBA编程，实现了一键自动生成报表的功能，利用VLOOKUP函数进行数据查找，配合VBA数组处理，将单元格数据转换为一维数组，优化效率。同时，还介绍了如何用VBA删除工作表中的空行，提升数据整理的自动化水平。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


Sub crDelReport()

    t1 = Timer
    Application.ScreenUpdating = False
        Call importLog
        Call findBrokenStation
        Call nowCrReport
        Call crFile
    Application.ScreenUpdating = True
    t2 = Timer
    Debug.Print "运行时间 = " & (t2 - t1) * 1000 & " ms"

End Sub
Sub crFile()

    Worksheets("结果统计-删除").Copy
    With ActiveSheet
        .Select
        .Columns("A:E").Delete
        .Shapes.Range(Array("Picture 1")).Delete
        [G1] = "执行结果"
        [G2] = "断站"
        [G3] = "执行成功"
        [G4] = "总计"
        
        [H1] = "数量"
        [H2].formula = "=COUNTIF(E:E,G2)"
        [H3].formula = "=COUNTIF(E:E,G3)"
        [H4].formula = "=SUM(H2:H3)"
    End With
'    格式化
    Call formatting
    
    ActiveWorkbook.SaveAs "XXXX测量配置结果_" & Month(Date) & "月第四组.xlsx"
    
End Sub

Sub nowCrReport()
Application.ScreenUpdating = False
    Dim d As Object, rng As Range
    Set dCity = CreateObject("Scripting.Dictionary")
    Set