本文介绍了删除空单元格数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我粘贴在一列的阵列,问题是,它留下了一些细胞在列空。我怎样才能消除这些细胞在列?

这是我有:

 私人小组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一些问题。


  1. 我们希望,这是在信息工作表中的code片。你不应该试图 .Activate 另一个工作表从 Worksheet_Change 事件宏。

  2. 为什么你需要这在 Worksheet_Change 事件宏目前尚不清楚。由于-是,这将运行任何时候在任何地方工作表中的值增加/修改/删除。这听起来有点小题大做,因为这决定胜负的只有两列列A和L。

  3. 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.

  1. Hopefully, this is on the Info worksheet's code sheet. You should not be attempting to .Activate another worksheet from the Worksheet_Change event macro.
  2. 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.
  3. 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.

这篇关于删除空单元格数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-18 04:27
查看更多