VBA Question; Selecting Non-Blank Cells and Pasting as Values

[revolvermac]

Hello all,

New to the forum as a poster but I've made use of a lot of the excellent advice and VBA code posted here for some time. Often I'm able to find just about anything to fit my needs, but I have a bit of a challenging coding question for you today.

I have a self-populating table that uses the following array formula (thanks to Rick Rothstein) to look up values for range A3:A40 from another table on a separate worksheet:

{=IFERROR(INDEX(Table1[Temp Name],SMALL(IF(Table1

="TERM",ROW(A$1:A$17)),ROWS(A$1:A1)))," ")}


Once the name is retrieved, I use basic VLOOKUP formulae to dump data into the rest of the columns present based on cell values in column A.

I'm setting up a macro to allow the administrator of the table to clear anyone with the "TERM" code on Table1; however, before doing that I need to paste any existing "TERM" names that have been captured by the array formula as values so that they don't vanish once the names are cleared from the source table.

The problem is that the array formula exists in each row and I need to avoid any "blank" rows from being pasted as I need the formula for any future changes that occur to Table1. The table is a living document that will need to be cleared on a routine basis.

Hopefully this is clear. I have VBA to clear the rows of anyone with the "TERM" code, however I need something that will select only cells that have data and aren't "blank". When I say "blank", I'm not talking about cells that are truly empty. The array formula simply returns a value of " " so that the cells LOOK blank. Right now I have the following set up:

Sub LockTerminated()
'
' LockTerminated Macro
'
'
    Sheets("Inactive").Select
    Range("Terminated").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=True, Transpose:=False
End Sub

The problem with this of course is that there's nothing to dictate the selection of cells with values that aren't " ". That's the missing piece of this puzzle and from other threads I've looked at, this isn't an easy thing to work around.

Please ask for any clarification as I know this is a bit confusing - your help is greatly appreciated! :)