gpt4 book ai didi

excel - 如何根据单元格值插入空白行

转载 作者:行者123 更新时间:2023-12-04 21:09:40 24 4
gpt4 key购买 nike

我正在尝试在 C 列中查找单元格值,如果它不包含点(“。”),则在单元格上方插入一个空白行。
我尝试了以下宏:

Sub testing()
Dim col As Variant
Dim lr As Long
Dim i As Long
Dim startRow As Long

col = "C"
startRow = 2
lr = Cells(Rows.Count, col).End(xlUp).Row

With ActiveSheet
For i = lr To startRow Step -1
If IsNumeric(Range("E2", "E" & lr).Value) = True Then
.Cells(i + 1, col).EntireRow.Insert shift:=xlUp
End If
Next i
End With
End Sub
输入
Input
所需输出
Output

最佳答案

插入发生在末尾的稍微不同的方法:

Sub Tester()

Dim c As Range, rng As Range, ws As Worksheet, v

Set ws = ActiveSheet

For Each c In ws.Range("C2:C" & ws.Cells(Rows.Count, "C").End(xlUp).Row).Cells
v = c.Value
If InStr(v, ".") = 0 And Len(v) > 0 Then 'has a value, with no "." ?
If rng Is Nothing Then 'any previous cells found?
Set rng = c 'start the range with `c`
Else
Set rng = Application.Union(c, rng) 'add `c` to `rng`
End If
End If
Next c
'if found any cells then do the insert
If Not rng Is Nothing Then rng.EntireRow.Insert shift:=xlDown
End Sub

关于excel - 如何根据单元格值插入空白行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/71575425/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com