Copy Range with Blank formula still shows in COUNT

[uk747]

Hi

I'm copying Ranges using VBA or even manually and even if the result of a formula in the range is "" when pasted it shows up in the COUNT When I select the Column.

Only way to stop it showing in the Count is Filter the Column for Blanks, select them all and press delete

e.g.
Cell B2 is a, Cell B3 is b, Cell B4 is = =IF(B2="a","",B2)

When I highlight Column B the Count is 3 which is fine 2 values and a formula
However when I copy B2:B4 and Paste as Values in D2, when I highlight Colum D the count is still 3 even though Cell D4 shows as a blank when you select it. If I click on D4 and Press delete it shows as 2.

So when I copy Paste the Blank which is the result of a formula how can I paste so that it wont show up in a count when column is Selected

Code used in example above

Range("B2:B4").Copy
Range("D2").PasteSpecial Paste:=xlPasteValues

Thanks

 

[Fluff]

You need to copy values like

Range("D2:D4").Value = Range("B2:B4").Value

 

[uk747]

Thanks but one of issues is, not sure how long initial range to copy is so could be D2:D50

Can't use current region as other values are next to it and I also format the range in separate line when copied e.g. .PasteSpecial Paste:=xlPasteFormats

Thought there was another way of it not appearing within the count when copy pasting blanks as a result of a formula

 

[Fluff]

You need to copy values, if you copy/paste as value, then you will get ""

 

[uk747]

You need to copy values, if you copy/paste as value, then you will get ""

ok thanks. Ill move some of other ranges out of way and maybe use currentregion

 

[Fluff]

Ok

 

[kvsrinivasamurthy]

Try

Sub test1()
Dim k&
k = WorksheetFunction.CountA(Range("B:B"))
Range("D2").Resize(k, 1).Value = Range("B2").Resize(k, 1).Value
End Sub

 

[Alex Blakenburg]

Thanks but one of issues is, not sure how long initial range to copy is so could be D2:D50

Can't use current region as other values are next to it and I also format the range in separate line when copied e.g. .PasteSpecial Paste:=xlPasteFormats

I think there a couple of other question bundled into this.
See if this gives you any ideas:

Sub CopyValues()

    Dim LastRow As Long
    
    ' Get Last Row of Copy Range
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    ' Copy Values Only and Convert "" to Blank (per Fluff)
    Range("D2:D" & LastRow).Value = Range("B2:B" & LastRow).Value
    
    ' Copy Format only
    Range("B2:B" & LastRow).Copy
    Range("D2:D" & LastRow).PasteSpecial Paste:=xlPasteFormats

End Sub

 

[uk747]

Thanks all. Extra options from kvsrinivasamurthy and Alex should cover what's needed

 

[MARK858]

Just another generic resize option for the value = value

Sub CopyValue()
    With Range("B2:B" & Range("B" & Rows.count).End(xlUp).Row)
        Range("D2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub