ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ±µÄò°O¿ý¸ê®Æ

[µo°Ý] ±µÄò°O¿ý¸ê®Æ

¥»©«³Ì«á¥Ñ PJChen ©ó 2019-9-9 17:11 ½s¿è

½Ð±Ð°ª¤â­Ì¡G
§Ú·Q¦b¦bKÄæŪ¨úA~Gªº¸ê®Æ
¨Ì§Ç¬°A¡BB¡BC¡KÄæ¦ìªº¶¶§Ç
¨C­ÓÄæ¦ìªºÅª¨ú¬°±q¤W¨ì¤U
¼Æ¾Úªø«×¤£¤@©w,¦³¥i¯à¼W¥[
Ū¨ú®É¡A¨C¤@Äæ¹J0©ÎªÅ®æ¡A¦Û°ÊŪ¨ú¤U¤@Äæ
¦ý¦bKÄ檺Åã¥Üµ²ªG³£­n¤@ª½¼W¥[¤U¥h
¤]´N¬O·í¸ê®ÆÄæ¼Æ©Î¦C¼Æ¼W¥[®É,KÄæ¸ê®Æ³£­n¯àŪ¨ú¨ì¨Ã±µÄò°O¿ý
§Ú·Q¥ÎVBA¼g³oºØŪ¨ú¤è¦¡¡A½Ð°Ý­n«ç»ò¼g³oºØ
20190909.170733.jpg

KÄæ±µÄò°O¿ý.rar (7.66 KB)

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 2# hcm19522

·PÁ§Aªº¦^ÂÐ,¦ý§Ú·Q¾ÇVBA»yªk
·Pı¦b³o¸Ìµo°Ý¡A¶V¨Ó¶V±o¤£¨ì¦^À³¤F....

TOP

¦^´_ 1# PJChen

KÄæ±µÄò°O¿ý.zip (103.25 KB)

    Sub test_20190914()
    '½T»{ Åã¥Üµ²ªG ªº³Ì«á¤@Äæ¦ì¸m
    ROW1 = Sheets("Åã¥Üµ²ªG").Cells(Rows.Count, "A").End(3).Row
   
    '¦pªG³Ì«á¤@Äæ > 1ªí¥Ü¦³¸ê®Æ
    If ROW1 > 1 Then
        '±N¸ê®Æ²M°£
        Sheets("Åã¥Üµ²ªG").Range("A2:A" & ROW1).Clear
    End If
   
    '§PÂ_¦³´X¦C=========
    COL1 = Range("A1").End(xlToRight).Column
   
    'Åã¥Üµ²ªG ¥Ñ²Ä2¦C¶}©l===================
    k = 2
   
    '¦Cªº°j°é==========
    For i = 1 To COL1
        
        '§PÂ_¨C¤@Ä檺³Ì«á¤@­Ó¦ì¸m
        ROW2 = Cells(Rows.Count, i).End(3).Row
        'Ä檺°j°é==========
        For j = 1 To ROW2
            
            '§PÂ_Äæ¦ì¤º®e ¤£¬O "0" ªº®É­Ô°õ¦æ ¤º®e
            If Cells(j, i) <> 0 Then
                '±NÄæ¦ìªº­È µ¹ Åã¥Üµ²ªG ªºÄæ¦ì
                Sheets("Åã¥Üµ²ªG").Cells(k, "A").Value = Cells(j, i).Value
                '©¹¤U¥[¤@Äæ
                k = k + 1
            End If
        
        Next
    Next

End Sub

TOP

¦^´_ 4# kim223824

¯u·PÁ§A,¥Ø«e´ú¸ÕOK,§Ú¦A¦n¦n¬ã¨s¤@¤U,¦³°ÝÃDªº¸Ü¦A½Ð±Ð§A¡C

¦A¦¸ÁÂÁÂ....

TOP

¤]¥i¥H³o¼Ë¼g
  1. Option Explicit
  2. Sub EX()
  3.     Dim Rng As Range, M As Variant, c As Range, r As Range
  4.     Set Rng = Sheets("KÄæŪ¨ú¸ê®Æ").Range("a1").CurrentRegion
  5.     M = "Åã¥Üµ²ªG"
  6.     For Each c In Rng.Columns   'Columns ½d³òªºColumn¶°¦Xª«¥ó
  7.         For Each r In c.Cells   'Cells ½d³òªºCell¶°¦Xª«¥ó
  8.             If r = "" Or r = 0 Then Exit For
  9.             M = M & "," & r
  10.         Next
  11.     Next
  12.     M = Split(M, ",")
  13.     With Sheets("Åã¥Üµ²ªG").Range("a1")
  14.         .CurrentRegion.Clear
  15.         .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
  16.     End With
  17. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

Sub TEST01()
Dim Arr, Brr, r&, c%, N&
[K2:K20000].ClearContents
Arr = Intersect([A:G], ActiveSheet.UsedRange)
ReDim Brr(1 To UBound(Arr) * UBound(Arr, 2), 0)
For c = 1 To UBound(Arr, 2)
For r = 1 To UBound(Arr)
    If Arr(r, c) = 0 Or Arr(r, c) = "" Then Exit For
    N = N + 1: Brr(N, 0) = Arr(r, c)
Next r
Next c
If N > 0 Then [K2].Resize(N) = Brr
End Sub


====================

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2019-9-16 13:48 ½s¿è

Sub TEST02()
Dim xC As Range, xR As Range, N&
[K2:K20000].ClearContents
For Each xC In [A1].CurrentRegion.Columns
For Each xR In xC.Cells
    If xR = 0 Or xR = "" Then Exit For
    N = N + 1: [K2].Cells(N, 1) = xR
Next: Next
End Sub

=======================

TOP

¦^´_ 6# GBKEE
¦^´_ 8# ­ã´£³¡ªL

·PÁ¤G¦ì¤j¤j¼ö±¡ªº´£¨Ñµ{¦¡¡A¨Ñ§Ú±o¥H¬ã¨s¤TºØ¤£¦P¼gªk¡A°õ¦æ¯uªº«Ü§Ö³t¡A·PÁÂ+++
<(¡Ã¡`¡Ã)>

TOP

¦^´_ 6# GBKEE

½Ð±ÐG¤j¡G
¸ê®Æ¤¤ªºªíÀY¦]¬°¦³±a¤½¦¡,²Ä¤@¦Cªº¥N¸¹,§Ú¤£·Q¸Ó¥¦Åã¥Ü¦bŪ¨úªºÄæ¦ì(aw)¤¤,½Ð°Ý­n¦p¦ó­×§ï³o¥y Set Rng = Sh.Range("aw2").CurrentRegion,Åý¥¦¤£Åª¨ú²Ä1¦C?

20190916.234903.jpg
2019-9-16 23:54
  1. Sub Ex()
  2. Dim Rng As Range, M As Variant, c As Range, r As Range, W As Workbook, Sh As Worksheet
  3.     Set W = Workbooks("³Ì·s®w¦s.xlsx")
  4.     Set Sh = W.Sheets("¼t¯Êªí")
  5. '------------------------------GBKEE
  6.     Set Rng = Sh.Range("aw2").CurrentRegion '¨Ó·½°Ï°ì ±qaw2~²{¦æ¨Ï¥Î°Ï¶ô
  7.     M = "¼t¯ÊText"
  8.     For Each c In Rng.Columns   'Columns ½d³òªºColumn¶°¦Xª«¥ó
  9.         For Each r In c.Cells   'Cells ½d³òªºCell¶°¦Xª«¥ó
  10.             If r = "" Or r = 0 Then Exit For   '¹JªÅ®æor 0µ{¦¡«K¸õ¤U¤@Äæ,ª½¨ì¸ê®Æµ²§ô
  11.             M = M & "," & r
  12.         Next
  13.     Next
  14.     M = Split(M, ",")
  15.     With Sh.Range("bm1")  '±NŪ¨ú¸ê®Æ,Åã¥Ü¨ìBMÄæ
  16.         .CurrentRegion.Clear
  17.         .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
  18.     End With
  19. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD