excel的一些vba命令

以下是一些VBA的命令，用在excel表格里的。

Sub 一步命令()
Dim rng As Range
Dim cell As Range
Dim contentToRemove As String

'禁用屏幕更新
Application.ScreenUpdating = False

'设置要替换的范围为C列的所有单元格
Set rng = Range("C:C")

'循环遍历每个单元格
For Each cell In rng
    '删除换行符
    cell.Value = Replace(cell.Value, vbLf, "")

    '将两个连续的双引号""替换为一个逗号","
    cell.Value = Replace(cell.Value, """""", ",")

    '删除单引号
    cell.Value = Replace(cell.Value, """", "")

    '要删除的特定内容
    contentToRemove = "要替换的内容"
    cell.Value = Replace(cell.Value, contentToRemove, "")

    '将斜杠符号"\"替换为反斜杠符号"/"
    cell.Value = Replace(cell.Value, "\", "/")
Next cell

'启用屏幕更新
Application.ScreenUpdating = True
End Sub

Sub DivideBy7_19()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    ' 设置要操作的工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' 设置要操作的范围，这里假设B列的数据从第1行开始
    Set rng = ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)

    ' 循环遍历范围中的每个单元格
    For Each cell In rng
        ' 检查单元格中是否为数字
        If IsNumeric(cell.Value) Then
            ' 将数字除以7.19并保留一位小数
            cell.Value = Round(cell.Value / 7.19, 1)
        End If
    Next cell
End Sub

保留第1、2、7、8和12行

Sub FilterA1()
    Dim rng As Range
    Dim cell As Range
    Dim arrLines As Variant
    Dim i As Integer

    '设置要筛选的范围为A列
    Set rng = Range("A:A")

    '循环遍历每个单元格
    For Each cell In rng
        '将单元格内容按换行符分割成数组
        arrLines = Split(cell.Value, Chr(10))

        '清空单元格内容
        cell.Value = ""

        '保留1，2，7，8，12行的内容
        For i = 0 To UBound(arrLines)
            If i = 0 Or i = 1 Or i = 6 Or i = 7 Or i = 11 Then
                cell.Value = cell.Value & arrLines(i) & Chr(10)
            End If
        Next i
    Next cell
End Sub

每个单元格的最后一行中的数字提取到相邻的B列，并移除RMB和数字之间的逗号

Sub 提取并移动数字()
    Dim cell As Range
    Dim lines() As String
    Dim lastLine As String
    Dim number As String
    Dim i As Integer

    For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        lines = Split(cell.Value, Chr(10))
        lastLine = lines(UBound(lines))

        '从最后一行提取数字
        number = ""
        For i = 1 To Len(lastLine)
            If IsNumeric(Mid(lastLine, i, 1)) Then
                number = number & Mid(lastLine, i, 1)
            End If
        Next i

        '移除"RMB"和数字之间的逗号
        lastLine = Replace(lastLine, "RMB ", "RMB")
        lastLine = Replace(lastLine, ", " & number, " " & number)

        '将数字移动到B列
        cell.Offset(0, 1).Value = number

        '更新单元格数值，不包括最后一行
        ReDim Preserve lines(UBound(lines) - 1)
        cell.Value = Join(lines, Chr(10))
    Next cell
End Sub