excel表格vba代码

以下是一些Excel表格VBA代码的示例：

1. 获取工作簿中指定工作表的数据并存入数组：

Function get_arr(file, sh_name)
    Dim wb As Workbook
    Set wb = Workbooks.Open(file)
    wb.Sheets(sh_name).Select
    row_num = \[b65536\].End(xlUp).Row
    col_num = ActiveSheet.UsedRange.Columns.Count
    col_str = get_col(col_num) '获取列名称
    arr = Sheets(sh_name).Range("a1:" & col_str & row_num)
    wb.Close False
    Set wb = Nothing
    get_arr = arr
End Function

2. 将当前工作表中的公式转换为数值：

Sub shuzhi()
    row_num = \[a65536\].End(xlUp).Row
    col_num = ActiveSheet.UsedRange.Columns.Count
    col_str = get_col(col_num)
    Range("A1:" & col_str & row_num).Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

3. 添加新工作表并删除旧数据：

Function add_sheet(sh_name)
    '删除旧数据
    Application.DisplayAlerts = False
    For Each sht In Sheets
        If sht.Name = sh_name Then
            sht.Delete
        End If
    Next sht
    Application.DisplayAlerts = True
    
    '添加新工作表
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sh_name
End Function

Sub ffa()
    row_num = \[a65536\].End(xlUp).Row
    col_num = ActiveSheet.UsedRange.Columns.Count
    col_str = get_col(col_num)
    arr = Range("A1:" & col_str & row_num)
    add_sheet("删除后")
    Sheets("删除后").Range("a1").Resize(row_num, UBound(arr, 2)) = arr
    
    For i = row_num To 1 Step -1
        If Cells(1, i) = "同比" Or Cells(1, i) = "" Then '此处填写条件
            Columns(i).Delete
        End If
    Next i
End Sub

希望以上代码能够帮到您。如果您有其他问题，请随时提问。
#### 引用[.reference_title]
- *1* *2* *3* [OFFICE Excel表格中常用的vba代码集锦](https://blog.csdn.net/qq_41780234/article/details/98598325)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]