How to paste a single value into filtered cells only using macros?

Question

I copy one cell ("A2") in excel, then on a different Sheet I filter the first column with some values. In the second column I want to paste the value which I copy from A2. How can I do that using macros?

Col1    Col2
a   
b   
b   
a

At the end I want to see:

Col1    Col2
a        10
b   
b   
a        10

Sub Macro()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "a"
    Sheets("Sheet2").Select
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:="a"
    Range("B2:B5").Select
End Sub

The code is not complete of course.

You need to use `Range("B2:B5").SpecialCells(xlCellTypeVisible)` to only paste in visible cells. On the other hand, this [post](https://stackoverflow.com/a/10717999/7558682) is a must read for you. — Damian, Jul 26 '19 at 09:50

Pᴇʜ · Accepted Answer · 2019-07-26T12:21:19.470

3

Use Range.SpecialCells method after filtering to get only the visible cells of the filtered range.

You might benefit from reading How to avoid using Select in Excel VBA.

Option Explicit

Public Sub Macro()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim FilterRange As Range
    Set FilterRange = ws.Range("A1", ws.Cells(LastRow, "A"))

    FilterRange.AutoFilter Field:=1, Criteria1:="a"
    FilterRange.SpecialCells(xlCellTypeVisible).Offset(ColumnOffset:=1).Value = 10

    FilterRange.AutoFilter
End Sub

To exclude the header use

FilterRange.Resize(RowSize:=FilterRange.Rows.Count - 1).Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible).Offset(ColumnOffset:=1).Value = 10

Edit according comment:

Letting the FilterRange begin in A2 instead of A1 does not work because then the drop down box appears in the first data row (row 2) instead of the header row (row 1).

edited Jul 26 '19 at 12:21

answered Jul 26 '19 at 09:52

Pᴇʜ

56,719
10
49
73

Thanks a lot, I will take a closer look and accept it if it helps. – Jul 26 '19 at 10:00
It modifies the column name as well on the visible column, Col2 is being modified to 10. How can I avoid it? – Jul 26 '19 at 10:03
@NasimiEldarov add a line doing `ws.Range("B1") = "NameYourHeader"` and that would be it, else the code should change. – Damian Jul 26 '19 at 10:06
1

@Damian better then re-writing the header is to exclude it from the `FilterRange` by using `FilterRange.Resize(RowSize:=FilterRange.Rows.Count - 1).Offset(RowOffset:=1)`, see my edited answer. – Pᴇʜ Jul 26 '19 at 10:41
Wouldn't it be easier to just set the filtered range to `A2` – GMalc Jul 26 '19 at 12:03
@GMalc Then the drop down field would show up in the first data row and not in the header. It will assume row 2 as header then. `Autofilter` always takes the first row of the filter range as header. – Pᴇʜ Jul 26 '19 at 12:08
So if the first row is not the filtered value it will still paste the copied value into the adjacent cell. Sorry, in my test I have the first rows value = to the filtered value, so it worked fine. – GMalc Jul 26 '19 at 12:14
@GMalc See the picture in my edited answer. If you begin in A2 then the drop down appears in the wrong position. Also you see that row 2 is considered as header (its row number is black, not blue like the filtered data rows). So even it comes up with the expected result, this is more by accident than by having the correct approach. (It's like in math where the result can be correct but still get 0 points because of using the wrong approach.) – Pᴇʜ Jul 26 '19 at 12:27
I understand, if the value in the `A2` is "b", it will put "10" in the cell to the right. By properly using `Offset` and `Resize` to adjust the range for a header row, is the correct approach. Sorry to keep bothering you, but I noticed that you used `Resize - Offset`, I've used `Offset - Resize`, is one better than the other? – GMalc Jul 26 '19 at 13:38
@GMalc You laways need to `Resize` *before* you `Offset`, because imagine if your range that you want to `Offset` is from the very first row until the very last row in Excel. If you now try to `Offset` eg plus one row, then you wil get an *"out of range"* error because you exceed the amount of rows Excel has. If you `Resize` first (make your range one row shorter) and then `Offset` plus one row it will work properly. – Pᴇʜ Jul 29 '19 at 06:20

How to paste a single value into filtered cells only using macros?

1 Answers1