By Mejias
Excel产生Selection对象
选中不连续单元格
选择“go to special”
选择visuable cells only
Selection批量填充不连续单元格开头内容
开头添加一个“0”
Sub 填0()
For Each r In Selection.Rows
a = Sheet1.Range("j" & r.Row).Value
Sheet1.Range("j" & r.Row) = "0" & a
Next
End Sub
开头添加两个“0”
Sub 填00()
For Each r In Selection.Rows
a = Sheet1.Range("j" & r.Row).Value
Sheet1.Range("j" & r.Row) = "00" & a
Next
End Sub
Selection配合Mid()函数批量截取不连续单元格内容
Sub MIDD()
For Each r In Selection.Rows
a = Sheet1.Range("j" & r.Row).Value
b = Mid(a, 2) ‘去除每个单元格的字符串里第一个字符
Sheet1.Range("j" & r.Row) = b
Next
End Sub
Selection对象下不连续单元格复制粘贴到新的工作表
For Each r In Selection.Rows
MsgBox r.Row
Sheet7.Range("E" & r.Row & ":" & "P" & r.Row).Copy
LastrowRa = Sheets("Reviewed ASIN").Range("a500000").End(xlUp).Row
Sheets("Reviewed ASIN").Range("A" & LastrowRa + 1, "L" & LastrowRa + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
count = count + 1
Next
Selectio对象下不连续单元格的批量删除
1.不连续的单元格选择使用遍历计数器计算选中的行数
count = 0
For Each r In Selection.Rows
count = count + 1
Next
LoC = count '计算出选中的不连续区域的个数,也是下面存储行号的数组的最大下表值
2.使用数组保存初始Selection里的所有行号,简化操作
Dim h As Integer
h = 1 ‘数组的下标
ReDim array1(1 To LoC) As Intege'需要重新定义数组,否则会显示需要constant expression
For Each r In Selection.Rows
array1(h) = r.Row '存取选中区域的行号到数组,为了在删除行的同时直接通过改变行号值来操作
h = h + 1
Next
3.删除过程中监控数组每个对象的值实际的变化精准删除需要的行
删除的行数countP增加,后面的成员在删除时的最终行号为=(行号初始值 – countP)(操纵删除过程行号的变化准确删除不需要的行)
Dim countP as Integer
countP = 0
For j = 1 To LoC
Sheet7.Range("E" & array1(j) - countP & ":" & "P" & array1(j) - countP).Delete shift:=xlShiftUp ‘后面的每一行向前移动的值等于删除的次数
countP = countP + 1 ‘删除的次数加1
Next