Forum Discussion
NSK-Mar
Nov 17, 2019Copper Contributor
Paste TO visible cells only in a filtered cells only
I want to paste a formula or value in the visible cells of a filtered column. How to go about it? Thanks
Cangkir
Oct 09, 2022Brass Contributor
I'd like to add something to my answer above, about the “Sub CopyVisibleToVisible1” macro. If you want to use this frequently, you can assign the macro to a toolbar buttons. This is how:
- Copy the code:
Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column
Dim rngA As Range
Dim rngB As Range
Dim r As Range
Dim Title As String
Dim ra As Long
Dim rc As Long
On Error GoTo skip:
Title = "Copy Visible To Visible"
Set rngA = Application.Selection
Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
Set rngB = rngB.Cells(1, 1)
Application.ScreenUpdating = False
ra = rngA.Rows.Count
rc = rngA.Columns.Count
If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
For Each r In rngA.SpecialCells(xlCellTypeVisible)
rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
Do
Set rngB = rngB.Offset(1, 0)
Loop Until rngB.EntireRow.Hidden = False
Next
Application.GoTo rngB
Application.ScreenUpdating = True
Application.CutCopyMode = False
Exit Sub
skip:
If err.Number <> 424 Then
MsgBox "Error found: " & err.Description
End If
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
- Open VBA window (by pressing ALT+F11)
- Open PERSONAL.xlsb
- In module section, create a new module, say module1.
- Paste the code into module1.
Now assign the macro to a toolbar button. You can follow the instruction on this link:
https://www.excelcampus.com/vba/add-macro-buttons-excel-ribbon-toolbar/
The benefit of doing it this way:
- The macro is available on any open workbook & easy to access.
- You don’t need to put the macro on all your workbooks, just on PERSONAL.xlsb.
- rajm189Mar 30, 2023Copper Contributor
Cangkir hi this VBA is working fine, but it’s taking time if we are using in larger number of cells, can we get quicker VBA ?
- CangkirJun 27, 2023Brass Contributor
hi this VBA is working fine, but it’s taking time if we are using in larger number of cells, can we get quicker VBA ?
Sorry for the late reply.
It depends on your data.
- About how many rows is your data?
- Do you copy from filtered range?
- Does your data have multiline cells?
- dastridermanMar 23, 2023Copper Contributor
To just select visible cells - shortcut alt + semilcolon (alt + 😉 is the real MVP.