How to not overwrite cells in filtered table using copy and paste values

Hi, just yesterday ran into the same problem.

The problem with pasting multiple selection of cells over a filtered area overwriting hidden cells was well known to me. However, never expected the same issue to exist with one cell selection over a filtered area in combination with the new paste values only (ctrl+shift+v) or the paste special (ctrl+alt+v) / (ctrl)+function after pasting. Just pasting with no "special" selection works fine, pasting values only or formulas not...

Little rant: Would be great if microsoft could fix these issues. Especially for business users working on critical data in excel this is detremental.

All search seems there is no proper fix for this. Your friend will be "Select Visible Cells" function. Shortcut (alt+;) was strangely however not working for me. If you have the same issue: home > find & select > go to special / (ctrl+g) or (F5) / or put it to your quick access toolbar.

In case you need a shortcut fix in the meantime, until hopefully microsoft finally fixes this, AutoHotkey is your friend, my solution for paste values and paste special:

#Requires AutoHotkey v2.0

; Only active when Excel is the foreground window
#HotIf WinActive("ahk_exe EXCEL.EXE")

^+x:: {  ; Ctrl + Shift + X
    try {
        xl := ComObjActive("Excel.Application")  ; Get running Excel instance
        selCount := xl.Selection.Cells.Count     ; Count selected cells
    } catch {
        return  ; if Excel isn't available, do nothing
    }

    if (selCount > 1) {
        ; Step 1: Select Visible Cells Only (Home > Find > GoToSpecial)
        Send("!hfdsy")   ; Alt + H F D S Y
        Sleep 50
        Send("{Enter}")
        Sleep 50
    }

    ; Step 2: Paste Values (Ctrl + Shift + V)
    Send("^+v")
    return
}
#HotIf

---AND---

#Requires AutoHotkey v2.0

; Only active when Excel is the foreground window
#HotIf WinActive("ahk_exe EXCEL.EXE")

^+d:: {  ; Ctrl + Shift + D
    try {
        xl := ComObjActive("Excel.Application")  ; Get running Excel instance
        selCount := xl.Selection.Cells.Count     ; Count selected cells
    } catch {
        return  ; if Excel isn't available, do nothing
    }

    if (selCount > 1) {
        ; Step 1: Select Visible Cells Only (Home > Find > GoToSpecial)
        Send("!hfdsy")   ; Alt + H F D S Y
        Sleep 50
        Send("{Enter}")
        Sleep 50
    }

    ; Step 2: Paste Special (Ctrl + Alt + V)
    Send("^!v")  ; Ctrl + Alt + V
    return
}
#HotIf

---QuickAccess---

if you put the function on your quick access toolbar, Step 1 can be shortened:

    ; Step 1: Select Visible Cells Only (Alt + 3 on QAT)
    Send("!3")  ; Alt + 3
    Sleep 50    ; small pause

Victor107200 

I am familiar with this bug. I am a consultant and both me and my clients frequently work in excel.
On my end it seems to happen in regular (non table) cells as well when many users filter rows and use the "only show my own filters" option.

Victor107200Hello Victor, I have tried to reproduce your scenario. In both cases, only the visible cells were described with oK. I have tested under Excel for the Web. Which operating system and which Excel version are you working with? That would certainly be interesting for other testers.