VBA Script

[Gor_yee]

Hi all,

I have a column of cells that has blanks in it, could be 5 blanks/15
banks etc. How can i copy it and paste to a new sheet but skipping the
blanks by using VBA??

 

[Gord Dibben]

Sub Copy_Non_Blanks()
Dim cell As Range, tempR As Range
Dim wsnew As Worksheet
Dim rng As Range
With ActiveSheet
For Each cell In Selection
If cell.Value <> "" Then
If tempR Is Nothing Then
Set tempR = cell
Else
Set tempR = Union(tempR, cell)
End If
End If
Next cell
If tempR Is Nothing Then
MsgBox "There are no Blank cells " & _
"in the selected range."
End
End If
End With
Set wsnew = Sheets.Add
tempR.Copy Destination:=wsnew.Range("A1")
End Sub


Gord Dibben MS Excel MVP