提取数据的问题

bzzxsgzc

如图
请教怎么用公式把表中C4:J63区域中的非空数据提取到sheet2工作表的C列，谢谢！

wdx223

1. =INDIRECT("Sheet1!"&TEXT(AGGREGATE(15,0,IF(Sheet1!$C$4:$J$63<>"",ROW($4:$63)*10+COLUMN($C$3:$J$3),10099),ROW(A1)),"R#C0"),)

复制代码

1055751654

卡卡的=INDIRECT("sheet1!"&TEXT(SMALL(--TEXT((Sheet1!$C$4:$J$63>0)*ROW($4:$63)/1%+COLUMN(C:J),"[>63]#;!65536"),ROW(A1)),"r0c00"),)

bzzxsgzc

wdx223 发表于 2019-10-23 16:58

谢谢！但有错误值和最后的0值怎么处理

1055751654

代码不卡，
Sub 提取()
Dim rng As Range, a, b, c, d
For Each rng In Sheet1.[c4:j63]
If rng <> "" Then
   a = a + 1
If a = 1 Then
   Set b = rng
Else
    Set b = Union(b, rng)
        c = b.Address
    End If
End If
Next
For Each rngs In b
    d = d + 1
Sheet2.Cells(d, 3) = rngs
Next
End Sub

bzzxsgzc

1055751654 发表于 2019-10-23 23:22
代码不卡，
Sub 提取()
Dim rng As Range, a, b, c, d

谢谢！如果提取到sheet2工作表的C列第三行开始，代码怎么修改

1055751654

bzzxsgzc 发表于 2019-10-24 10:48
谢谢！如果提取到sheet2工作表的C列第三行开始，代码怎么修改

Sub 提取()
Dim rng As Range, a, b, c, d
For Each rng In Sheet1.[c4:j63]
If rng <> "" Then
   a = a + 1
If a = 1 Then
   Set b = rng
Else
    Set b = Union(b, rng)
        c = b.Address
    End If
End If
Next
For Each rngs In b
    d = d + 1
Sheet2.Cells(d, 3).Offset(2, 0) = rngs
Next
End Sub

1055751654

再来种方法，有点好玩
Sub 提取()
Dim rng As Range, a, b, s
For Each rng In Sheet1.[c4:j63]
    If rng <> "" Then
       a = rng
       b = b & rng & ","
   End If
Next
a = Split(b, ",")
For i = 0 To UBound(a)
s = s + 1
Sheet2.Cells(s, 3).Offset(2, 0) = a(i)
Next
End Sub

1055751654

再加一句清空语句比较好，执行前覆盖上次执行的

刚在小猪猪那学了段代码，很简短
Sub tiqu()
Sheet2.[c3:c65536].ClearContents
For x = 4 To 63
For y = 3 To 10
  If Sheet1.Cells(x, y) <> "" Then
    cnt = cnt + 1
    Cells(cnt, 3).Offset(2, 0) = Sheet1.Cells(x, y)
  End If
Next
Next
End Sub