Excel：从 VBA 宏中省略行/列

Excel: Omitting rows/columns from VBA macro

提问by toolshed

With some help, I've put together two functions that will work in unison to first convert all of my data from the "text" format to a "number" format. After which it will set each column to a fixed number of characters.

在一些帮助下，我将两个函数组合在一起，首先将我的所有数据从“文本”格式转换为“数字”格式。之后它会将每列设置为固定数量的字符。

The two sub-routines I'm using are listed below, but I can't figure out how to omit certain rows/columns for the respective functions.

下面列出了我正在使用的两个子例程，但我不知道如何为相应的函数省略某些行/列。

When running the psAdd function, I want to omit the first 3 rows from the range, and for the FormatFixedNumber function I want to omit several columns. The problem with the latter is that I have 1000+ columns of data and a key header row containing a 1 or 0 that represents whether the column should be converted.

运行 psAdd 函数时，我想省略范围中的前 3 行，而对于 FormatFixedNumber 函数，我想省略几列。后者的问题是我有 1000 多列数据和一个包含 1 或 0 的关键标题行，表示是否应转换该列。

How could modify this code to skip the first 3 rows in the first sub, and several columns marked with a 0 in the second?

如何修改此代码以跳过第一个子项中的前 3 行，以及第二个子项中标有 0 的几列？

Sub psAdd()  
    Dim x As Range 'Just a blank cell for variable
    Dim z As Range 'Selection to work with

    Set z = Cells
    Set x = Range("A65536").End(xlUp).Offset(1)
    If x <> "" Then
        Exit Sub
    Else
        x.Copy
        z.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
        Application.CutCopyMode = False 'Kill copy mode
    End If
    x.ClearContents 'Back to normal
End Sub

Sub FormatFixedNumber()

    Dim i As Long

    Application.ScreenUpdating = False
    For i = 1 To lastCol 'replace 10 by the index of the last column of your spreadsheet
        With Columns(i)
            .NumberFormat = String(.Cells(2, 1), "0") 'number length is in second row
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

回答by brettdj

1. First code

1.第一个代码

At the moment you are working on all the cells on a sheet with z. You can reduce this to the UsedRange- ignoring the first three rows by

目前，您正在使用z. 您可以将其减少到UsedRange- 忽略前三行

• forcing the UsedRangeto update before using it (to avoid redunant cells)
• testing if the zexceeds 3 rows

• if so resize zby three rows using Offsetand Resize

  Sub psAdd()
  Dim x As Range    'Just a blank cell for variable
  Dim z As Range    'Selection to work with
  ActiveSheet.UsedRange
  Set z = ActiveSheet.UsedRange
  If z.Rows.Count > 3 Then
      Set z = z.Cells(1).Offset(3, 0).Resize(z.Rows.Count - 3, z.Columns.Count)
  End If
  'using Rows is better than hard-coding 65536 (bottom of xl03 - but not xl07-10)
  Set x = Cells(Rows.Count,"A").End(xlUp).Offset(1)
  If x <> "" Then
      Exit Sub
  Else
      x.Copy
      z.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
      Application.CutCopyMode = False    'Kill copy mode
  End If
  x.ClearContents    'Back to normal
  End Sub
  

• UsedRange在使用之前强制更新（以避免冗余单元格）
• 测试是否z超过 3 行

• 如果是这样z，使用Offset和调整三行Resize

  Sub psAdd()
  Dim x As Range    'Just a blank cell for variable
  Dim z As Range    'Selection to work with
  ActiveSheet.UsedRange
  Set z = ActiveSheet.UsedRange
  If z.Rows.Count > 3 Then
      Set z = z.Cells(1).Offset(3, 0).Resize(z.Rows.Count - 3, z.Columns.Count)
  End If
  'using Rows is better than hard-coding 65536 (bottom of xl03 - but not xl07-10)
  Set x = Cells(Rows.Count,"A").End(xlUp).Offset(1)
  If x <> "" Then
      Exit Sub
  Else
      x.Copy
      z.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
      Application.CutCopyMode = False    'Kill copy mode
  End If
  x.ClearContents    'Back to normal
  End Sub
  

2. Second code

2. 第二个代码

Run a simple test on each header cell to proceed if it doesn't equal 0. Assuming that the header cell is in row 1 then

对每个标题单元格运行一个简单的测试，如果它不等于 0，则继续。假设标题单元格在第 1 行然后

Sub FormatFixedNumber()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To lastCol    'replace 10 by the index of the last column of your spreadsheet
        If Cells(1, i) <> 0 Then
            With Columns(i)
                .NumberFormat = String(.Cells(2, 1), "0")    'number length is in second row
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub