Copy Only Cells With Numeric Values Onto New Sheet

[GingerMermaid]

Hello Mr.Excel Gurus!

I am currently attempting to copy data from a column that has a mix of cell values; some are numbers, some are words, and some are blank. I am trying to create a list that will copy over only those cells with unique number values, ignoring the blank and word-filled cells.

I'm not sure if there is a way to make a dynamic range that will take into account the fact that there are some blank cells, but I'm certain that my range of data will never exceed cell A1000, so I am currently using that as the bottom of my range.

I have managed to create a loop that will copy over the unique values, but I'd appreciate it if you could help me modify it so that it only pulls over the numeric ones.

Here is what I have so far:

'activate worksheet w/ source data
wsSourceData.Activate

    'pull over unique list
    With ActiveSheet
        .Range("a1", Range("a1000")).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=wsUniqueData.Range("a1"), _
            Unique:=True
    End With

Thanks in advance for your assistance!!

 

[Smitty]

Welcome to the Board!

I'd think about adding a helper column using =ISNUMBER(), then AutoFilter off of that to display just the TRUE results.

As for a dynamic range, you can use:

Dim lr as Long
  lr = Cells(Rows.Count,"A").End(xlUp).Row

  With ActiveSheet
        .Range("A1", Range("A" & lr)).AdvancedFilter 
  '  And so on...

HTH,

 

[GingerMermaid]

Nice!! I think that bit of code is really going to come in handy for me! Thanks! I've been trying to figure out the ISNUMERIC function, but so far, I have not succeeded in getting it to do what you've suggested. :-/ Would you mind showing me how I would format the helper column?