copy only from a cell with data in it

[JoeH]

Here is my existing code if it helps any

Joe


Dim TestCalcs_rev22 As Workbook
Dim Bondsdb As Workbook
Dim shtinitial_information As Worksheet
Dim shtsheet1 As Worksheet
Dim cl As Integer

Private Sub UserForm_Activate()
With Workbooks("BondsDB.xls").Worksheets("sheet1")
For cl = 2 To 5000
If .Cells(cl, 4) = "" Then Exit For
Next
End With
cl = cl - 1
progListBox.RowSource = "[BondsDB.xls]sheet1!$A$2:$IF$" & Mid(Str(cl)
2)
End Sub


Private Sub btnOpen_Click()
If progListBox.ListIndex = -1 Then
Beep
MsgBox "No Reference Selected!", vbExclamation, ""
Exit Sub
End If
Set rng = Range(progListBox.RowSource).Columns(1).Cells
cl = rng.Offset(progListBox.ListIndex, 0).Row
With Workbooks("bondsdb.xls").Worksheets("sheet1")

Load:
'Range("Issr") = .Cells(cl, 1)
'Range("Issr_local") = .Cells(cl, 2)
'Range("Principal") = .Cells(cl, 4)
'Range("Term") = .Cells(cl, 7)
.Columns("AB:CI").Rows(cl).Copy
Workbooks("TestCalcs_rev22.xls").Worksheets("initial_information").Range("E30")
_
PasteSpecial xlPasteAll, Transpose:=True
'.Columns("Y:CF").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("initial_information").Range("").
'PasteSpecial xlPasteAll, Transpose:=True
'.Columns("EE:HZ").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("initial_information").Range("")
_
'PasteSpecial xlPasteAll, Transpose:=True


End With
Hide
End Sub

Private Sub btnCancel_Click()
Hide
End Su

 

[Bob Kilmer]

What about the code leaves you wanting? I see you are using "If
..Cells(cl, 4) = ""." Is that not working for you?

The way I check for a null string is:
If Len(.Cells(cl, 4).Text) < 1 Then ...

See the SpecialCells method of the Range class. It allows you get blank
cells, en masse. For example:

Dim r as Range
Set r = Columns("A:B").SpecialCells(xlCellTypeBlanks)

Also See IsEmpty(), and the other 'Is' functions, perhaps.

Sometimes it is useful to sort a range to remove the empties.