PDA

View Full Version : Solved: Select copy & paste



Gil
10-08-2009, 10:37 PM
I have a requirement to be able to copy & paste to a new sheet data in 7 columns by 3 rows ignoring the blank rows in between if possible. The number of jobs is variable from 1 to 100 or more. I have attached a sample workbook, the data between the red lines is what I would like copied. I have tried filtering the blanks but never with a satisfactory result.The Original sheet would be left in tact but would allow me to further edit and add data to the new sheet with a macro or other means that I already use.
I think I might be confusing myself with what I want to do so any help or direction will be appreciated.
1984

Bob Phillips
10-09-2009, 04:30 AM
Public Sub ProcessData()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B1").Resize(LastRow)
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

End Sub

Gil
10-12-2009, 02:27 PM
xld
Many thanks for your reply & solution. After a couple of days messing it all works as I wished for and I understand a little more VBA. Perhaps VBA for dummies will come my way for xmas.