VBA:Find last value in column

[Robert Mika]

What I'm trying to do is to find last value in column from which data are deliverd from formula.
Example
Column A has 20 rows with simple formula
=if(b1="","",b1)
....
...
....
=if(b20="","",b20)

The number of non blank cell can be difftrent each time and I woud like to copy only those which are non blank(by non-blank means they have value)
So far I got here:

Range("a2").Select
    
    Range("b2", Range("b2").End(xlDown)).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

but this copy all 20 rows not the one with values.

This bit:

SkipBlanks _
        :=True

does not work for what I'm trying to accomplish.

 

[Mike Blackman]

Hi,

Best way to find last row of data is to use xlUp not xlDown...

Range("A" & rows.count).End(xlup).Row

If your using PasteSpecial Values you will only bring across the values, blanks will come over as blanks, to remove the blanks from the destination list you could use the Sort function.

Hope this helps.

 

[Robert Mika]

Hi Mike,
Sorry for my ignorance.
What you have suggested did not work.
I assume I have entered that into wrong place replacing this part:

Range("e2", Range("e2").End(xlDown)).Copy

with your suggestion


My code so far look like this:

Sub GoToSingleColumn()
    
    Range("a2").Select
    
    Range("e2", Range("e2").End(xlDown)).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    ActiveCell.End(xlDown).Offset(1, 0).Select
    
    
    Range("f2", Range("f2").End(xlDown)).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ActiveCell.End(xlDown).Offset(1, 0).Select
    
    Range("g2", Range("g2").End(xlDown)).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ActiveCell.End(xlDown).Offset(1, 0).Select
    Range("a2").CurrentRegion.RemoveDuplicates Columns:=Array(1), _
        Header:=xlNo
        Range("a2").Select
    
    
    
End Sub

Copying three columns into one and then removing duplicates

But it still include one empty cell inbeetwee rows.