问题描述
我粘贴在一列的阵列,问题是,它留下了一些细胞在列空。我怎样才能消除这些细胞在列?
这是我有:
私人小组Worksheet_Change(BYVAL目标作为范围) 工作表(信息),范围(A1)。选择
昏暗我作为整数
昏暗iLastRow只要
iLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count,A)。完(xlUp).Row
昏暗的arrmatrix()作为字符串 使用ReDim arrmatrix(1至iLastRow,1比1)
对于i = 1到iLastRow
范围(A2)。细胞(I,1)。选择
如果Selection.Offset(0,11)=丕emitida然后
arrmatrix(I,1)=范围(A2)。细胞(I,1)。价值
万一
接下来,我
工作表(INICIO),范围(G4:G1000000)。clearContents中。 工作表(INICIO)。范围(G4)。调整尺寸(UBound函数(arrmatrix,1))。值= arrmatrix()分结束
有与样品code一些问题。
- 我们希望,这是在信息工作表中的code片。你不应该试图
.Activate
另一个工作表从Worksheet_Change
事件宏。 - 为什么你需要这在
Worksheet_Change
事件宏目前尚不清楚。由于-是,这将运行任何时候在任何地方工作表中的值增加/修改/删除。这听起来有点小题大做,因为这决定胜负的只有两列列A和L。 - ReDim语句可以使用的 preserve 的展开数组,但它只能REDIMENSION最后的排名。
该修改不依赖于选择或处理之前激活工作表(S)。在 arrmatrix 的数组作为需要这样你就不会在数组中的空值结束了扩大。
私人小组Worksheet_Change(BYVAL目标作为范围)
如果没有相交(目标,范围(A:A,L:L))是没有那么
对错误转到FIN
Application.EnableEvents =假
昏暗我一样长,N当
昏暗arrmatrix为Variant
使用ReDim arrmatrix(1对1,1对1)
对于i = 2细胞(Rows.Count,1).END(xlUp).Row
如果细胞(我12).value的=丕emitida然后
N = N + 1
使用ReDim preserve arrmatrix(1对1,1到n)
arrmatrix(1,N)=细胞(I,1)。价值
万一
接下来,我
随着工作表(INICIO)
.Range(G4:G&放大器; Rows.Count).ClearContents
.Range(G4)。调整(UBound函数(arrmatrix,2),1)= Application.Transpose(arrmatrix)
结束与
万一鳍:
Application.EnableEvents = TRUE
结束小组
这将只运行在添加列A或L列的值/修改/删除。
由于我一直在扩展和填充的最后一个级别,我用 Application.Transpose
来重新调整数据之前我塞进它放回 INICIO 工作
I paste an array in a column, the problem is that it leaves some cells in the column empty. How can I erase those cells in the column?
This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Info").Range("A1").Select
Dim i As Integer
Dim iLastRow As Long
iLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arrmatrix() As String
ReDim arrmatrix(1 To iLastRow, 1 To 1)
For i = 1 To iLastRow
Range("A2").Cells(i, 1).Select
If Selection.Offset(0, 11) = "Pi emitida" Then
arrmatrix(i, 1) = Range("A2").Cells(i, 1).Value
End If
Next i
Worksheets("Inicio").Range("G4:G1000000").ClearContents
Worksheets("Inicio").Range("G4").Resize(UBound(arrmatrix, 1)).Value = arrmatrix()
end sub
There are a few problems with your sample code.
- Hopefully, this is on the Info worksheet's code sheet. You should not be attempting to
.Activate
another worksheet from theWorksheet_Change
event macro. - It isn't clear why you need this in a
Worksheet_Change
event macro. As-is, this will run any time a value anywhere in the worksheet is added/modified/deleted. That sounds like overkill since the only two columns that determine the outcome are columns A and L. - The ReDim statement can be used with Preserve to expand the array but it can only redimension the last rank.
This modification does not rely on selecting or activating the worksheet(s) before processing them. The arrmatrix array is expanded as needed so you do not end up with blank values in the array.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A, L:L")) Is Nothing Then
On Error GoTo Fìn
Application.EnableEvents = False
Dim i As Long, n As Long
Dim arrmatrix As Variant
ReDim arrmatrix(1 To 1, 1 To 1)
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 12).Value = "Pi emitida" Then
n = n + 1
ReDim Preserve arrmatrix(1 To 1, 1 To n)
arrmatrix(1, n) = Cells(i, 1).Value
End If
Next i
With Worksheets("Inicio")
.Range("G4:G" & Rows.Count).ClearContents
.Range("G4").Resize(UBound(arrmatrix, 2), 1) = Application.Transpose(arrmatrix)
End With
End If
Fìn:
Application.EnableEvents = True
End Sub
This will only run when a value in column A or column L is added/changed/deleted.
Since I've been expanding and populating the last rank, I used Application.Transpose
to reorient the data before I stuffed it back into the Inicio worksheet.
这篇关于删除空单元格数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!