Sort and eliminate blanks

[alainfranco]

Hello,

Newby quesiton here to keep you on your toes!

How would I clean up a list in a column refered to by a named range, and copy the results to another column?

In other words, I have a list of fields whichm based on a vlookup, returns cities (or not) to column A. This list sometimes contains a result, sometimes not.

I need a clean list, meaning that I would need to have all blank resultst filtered out. But since I am using this result list as an input to another formula, I would need it to be cleared of blanks and copied over to the next column over.

Any advice on how to do this?

Thanks

 

[igold]

Does this work...

Sub CopyNoBlanks()


    With ActiveSheet.Range("NamedRange")  'Change to your Named Range
        .AutoFilter Field:=1, Criteria1:="<>"
        .SpecialCells(xlVisible).Copy
        .Offset(0, 1).PasteSpecial Paste:=xlPasteAll
        .Cells(1).Select
    End With
    ActiveSheet.ShowAllData
    ActiveSheet.AutoFilterMode = False


End Sub

 

[alainfranco]

Does this work...

Sub CopyNoBlanks()


    With ActiveSheet.Range("NamedRange")  'Change to your Named Range
        .AutoFilter Field:=1, Criteria1:="<>"
        .SpecialCells(xlVisible).Copy
        .Offset(0, 1).PasteSpecial Paste:=xlPasteAll
        .Cells(1).Select
    End With
    ActiveSheet.ShowAllData
    ActiveSheet.AutoFilterMode = False


End Sub

Hi,

Sorry for the delay, I was away.

No, it does not work. at least not completly.

It does copy information over one column and does seem to sort to a certain level, but because the sorted data is the result of a formula, the macro copies the formula, which is not exactly the intended objective.

Any other idea friend?

 

[igold]

Other than in the title of your post you did not specify what kind of sort you wanted. If you provide the criteria for the sort, it could be written into the code.

Do you want just the values copied over or exactly what. If you could articulate what is exactly the intended objective, that would go a long way toward a successful conclusion.