copy range, skip empty cells

[choubix]

hello,

I would need to copy a range from on location to another skipping all empty cells.

is there a way to do that without using loops?? (the pastespecial skip blank cells doesnt work here as its purpose is different...)

thanks

[Bob Phillips]

Sub CopyData()
Dim rng As Range

Set rng = AntiUnion(Range("A1:A5"), Range("A1:A5").SpecialCells(xlCellTypeBlanks))
rng.Copy Range("M1")
End Sub


'-----------------------------------------------------------------
Function NotUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set NotUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function

[choubix]

hi xld,

just had to change the name of the function. it works fine thx :)

just one more thing:
right now the function returns the result. is it possible to get the address of the copied cells instead ?


thanks

[Bob Phillips]

Just do a

rng.Address

[choubix]

it returns an error (invalid use of property)

[Bob Phillips]

Sub CopyData()
Dim rng As Range

On Error Resume Next
Set rng = Range("A1:A5").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox NotUnion(Range("A1:A5"), rng)
End If
End Sub


'-----------------------------------------------------------------
Function NotUnion(SetRange As Range, UsedRange As Range, _
Optional RowAbsolute As Boolean = True, _
Optional ColumnAbsolute As Boolean = True, _
Optional ReferenceStyle As XlReferenceStyle = xlA1, _
Optional External As Boolean = False) As String
'-----------------------------------------------------------------
Dim saveSet
Dim rng As Range
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set rng = SetRange.SpecialCells(xlCellTypeBlanks)
NotUnion = rng.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External)
SetRange = saveSet
End Function

[jdp]

Hi XLD,
I have a similiar problem and wonder if you could help me too? I am not good in VBA and would really appreciate your assistance! I have a a row of data which have data in it and some of the rows are 0. I need to copy the rows that are not zero into a new sheet.

2013/08/30 17:25 37666 37690 37585 37632 37690 37690 37585 37585
2013/08/30 16:40 37735 37735 37650 37688 37735 37735 37650 37650
2013/08/30 16:35 37740 37750 37721 37721 0 0 0 0
2013/08/30 16:30 37694 37761 37679 37748 37761 37761 37679 37679

Into a row like this where the row 0 is deleted:
2013/08/30 17:25 37666 37690 37585 37632 37690 37690 37585 37585
2013/08/30 17:20 37735 37735 37650 37688 37740 37740 37670 37670
2013/08/30 16:30 37694 37761 37679 37748 37761 37761 37679 37679

Is it possible to ask for working excel sheet where i can adjust only the ranges?
thank you