EXCEL怎么將復(fù)制的內(nèi)容粘貼到可見單元格

1、本方法要使用EXCEL VBA宏代碼進(jìn)行操作，代碼如下：Option ExplicitSub 復(fù)制粘貼可見單元格() Dim rgSrc As RangeDim rgDes As RangeDim rg As Range, rgPt As RangeDim strSrcAdd() As StringDim lCnt As Long, i As Long, j As Long, x As Long, y As LongOn Error GoTo ExitPointSet rgSrc = Application.InputBox("請(qǐng)選擇要復(fù)制的單元格區(qū)域", &qu

2、ot;提示", , , , , , 8)If rgSrc.Columns.Count > rgSrc.Parent.UsedRange.Columns.Count ThenSet rgSrc = rgSrc.Parent.Range(rgSrc.Item(1, 1), rgSrc.Item(rgSrc.Rows.Count, rgSrc.Parent.UsedRange.Columns.Count)End IfIf rgSrc.Rows.Count > rgSrc.Parent.UsedRange.Rows.Count ThenSet rgSrc = rgSrc.Pare

3、nt.Range(rgSrc.Item(1, 1), rgSrc.Item(rgSrc.Parent.UsedRange.Rows.Count, rgSrc.Columns.Count)End IfSet rgDes = Application.InputBox("請(qǐng)選擇要粘貼的單元格位置", "提示", , , , , , 8)Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualSet rgPt = rgDes.Cells(1, 1)ReDim s

4、trSrcAdd(0)For Each rg In rgSrcIf Not (rg.Height = 0 Or rg.Width = 0) ThenReDim Preserve strSrcAdd(UBound(strSrcAdd) + 1)strSrcAdd(UBound(strSrcAdd) = rg.AddressEnd IfNext rglCnt = 0For Each rg In rgSrc.Parent.Range(rgSrc.Rows(1).Address)If rg.Width <> 0 ThenlCnt = lCnt + 1End IfNextstrSrcAdd(

5、0) = lCnti = 0j = 0x = 0y = 0For lCnt = 1 To UBound(strSrcAdd)lp: If (lCnt - 1) strSrcAdd(0) + i > x Thenj = 0End Ifx = (lCnt - 1) strSrcAdd(0) + iy = (lCnt - 1) Mod strSrcAdd(0) + jSet rg = rgPt.Offset(x, y)If rg.Width = 0 Thenj = j + 1GoTo lpElseIf rg.Height = 0 Theni = i + 1j = 0GoTo lpElserg.

6、Value = rgSrc.Parent.Range(strSrcAdd(lCnt)End IfNext lCntrgDes.Parent.ActivateExitPoint:Application.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticEnd Sub下面將以一個(gè)示例的方式，演示如何使用以上代碼。圖中標(biāo)黃色的為要隱藏的單元格，隱藏前后效果如圖所示。在相應(yīng)的工作表上點(diǎn)擊右鍵，在彈出的窗口中選擇“查看代碼”在打開的VBE界面中粘貼以上復(fù)制的代碼。在“查看宏”對(duì)話框中選擇并運(yùn)行名為“復(fù)制粘貼可見單元格”的宏。宏的使用方法，請(qǐng)另外百度。先選