View Full Version : copy range, skip empty cells
choubix
06-22-2008, 01:30 AM
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
06-22-2008, 01:50 AM
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
06-22-2008, 02:07 AM
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
06-22-2008, 02:58 AM
Just do a
rng.Address
choubix
06-22-2008, 03:30 AM
it returns an error (invalid use of property)
Bob Phillips
06-22-2008, 04:23 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.