我試圖找出一種方法來復制和粘貼過濾列中的值并粘貼到同一索引但不同的列上。看看下面它應該如何作業：
原版的：
| 排 | A欄 | B欄 |
|---|---|---|
| 1 | A B C D | |
| 4 | acdf | |
| 9 | gfac |
運行 VBA：
| 排 | A欄 | B欄 |
|---|---|---|
| 1 | A B C D | A B C D |
| 4 | acdf | acdf |
| 9 | gfac | gfac |
我嘗試了SpecialCells(xlCellTypeVisible)但我無法讓它作業，因為我只需要復制一個特定的列而不是整個作業表......有人可以幫助我嗎？謝謝！
uj5u.com熱心網友回復：
請測驗下一個代碼。它首先從過濾的可見單元格范圍創建一個列切片，然后處理每個區域行：
Sub testCopyFilteredRangeAToB()
Dim sh As Worksheet, rngF As Range, ar As Range, R As Range
Set sh = ActiveSheet
On Error Resume Next 'to avoid an error in case of no any visible cells
Set rngF = sh.UsedRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then Exit Sub 'if not range created, exit the code
Set rngF = Intersect(sh.UsedRange.SpecialCells(xlCellTypeVisible), sh.Range("A:B"))
For Each ar In rngF.Areas 'iterate between the range areas
For Each R In ar.rows 'iterate between the area rows
R.cells(1, 2).value = R.cells(1, 1).value 'copy the values, without using clipboard
Next R
Next ar
End Sub
uj5u.com熱心網友回復：
過濾列到過濾列
Option Explicit
Sub FilteredColumnToFilteredColumn()
'*** (three lines) is indicating kind of the preferred order
Const sCol As Long = 1 ' Source Column - being read from
Const dCol As Long = 2 ' Destination Column - being written to
Dim ws As Worksheet: Set ws = ActiveSheet
'If ws.FilterMode Then ws.ShowAllData '***
Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion ' Range (has headers)
Dim dcrg As Range ' Data Column Range (no headers)
Set dcrg = rg.Resize(rg.Rows.Count - 1).Offset(1).Columns(dCol)
'rg.AutoFilter X, "Yes" '***
Dim vdcrg As Range ' Visible Data Column Range
On Error Resume Next
Set vdcrg = dcrg.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
ws.AutoFilterMode = False '***
If vdcrg Is Nothing Then Exit Sub
vdcrg.Formula = "=" & vdcrg.Cells(1).EntireRow.Columns(sCol).Address(0, 0)
' Note that you cannot do: 'vdcrg.Value = vdcrg.Value'
' This won't work if you haven't set the 'AutoFilterMode' to 'False'.
' Also, it is assumed that the whole column contains values, not formulas.
' Why would you write values to some of its cells if it contained formulas?
dcrg.Value = dcrg.Value
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/443978.html