Hi all trying to join SpecialCells(xlConstants) with SpecialCells(xlFormulas)

[Pfereday]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Hi all I'm trying to join SpecialCells(xlConstants) with SpecialCells(xlFormulas) and no matter what I [/FONT]try I can only one or the other to work never both.

Sub Done()
Dim NR As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Your Quotation")
    NR = .Range("D" & .Rows.Count).End(xlUp).Row + 1
    If NR > 149 Then NR = NR + 1    .Range("C" & NR).Resize(, 1).Value = Range("B:C").Value
    
    
    Range("A:A").SpecialCells(xlConstants & xlFormulas).Offset(, 2).Copy .Range("C" & NR)
    Range("A:A").SpecialCells(xlConstants).Offset(, 3).Copy .Range("D" & NR)
    Range("E:E").SpecialCells(xlConstants).Offset(, 1).Copy .Range("D" & NR)
    
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Beep
End Sub

 

[Fluff]

How about

Union(Range("A:A").SpecialCells(xlConstants), Range("A:A").SpecialCells(xlFormulas)).Offset(, 2).Copy Range("C" & nr)

But if you do not have both formulae & constants in the column it will fail

 

[Pfereday]

[TABLE="width: 256"]
<colgroup><col></colgroup><tbody>[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]=IF(C4 ="Three section bay","a","")[/TD]
[/TR]
[TR]
[TD]a
hi all the above are in column a and your union didn't work[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

In what way "didn't it work"?

 

[Pfereday]

Hi only the cell next to the a's copied the next to the formula didn't didn't copy sorry for the slow reply but I have gone for a pint and replying with my phone.

 

[Fluff]

Those cells that didn't copy, are they formulae or hard values?
If formulae did the formula copy over, but now returning ""?

 

[Pfereday]

Everything that I am trying to copy is a hard value, it's a pick list and the a,s represent ticks, it was the formula that had a or tick webdings font that is not copying the hard value in the cell next to it all the others a,s or ticks if you prefer copied the appropriate cells next to them.

 

[Fluff]

Are you trying to copy the contents of col B or col C?

 

[Pfereday]

Col c

 

[Rick Rothstein]

Why not just copy the values in the column (maybe intersected with the UsedRange to keep size down) to your new location and then use SpecialCells to delete the blank cells at the copied location shifting everything in the column upwards?