copy only from a cell with data in it
 

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("initi al_information").Range("E30")
_
PasteSpecial xlPasteAll, Transpose:=True
'.Columns("Y:CF").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range("").
'PasteSpecial xlPasteAll, Transpose:=True
'.Columns("EE:HZ").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range("")
_
'PasteSpecial xlPasteAll, Transpose:=True


End With
Hide
End Sub

Private Sub btnCancel_Click()
Hide
End Su

--
Joe
-----------------------------------------------------------------------
JoeH's Profile: http://www.excelforum.com/member.php...fo&userid=1413
View this thread: http://www.excelforum.com/showthread.php?threadid=26267

copy only from a cell with data in it
 

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.


JoeH wrote:

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("initi al_information").Range("E30").
_
PasteSpecial xlPasteAll, Transpose:=True
'.Columns("Y:CF").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range("").
'PasteSpecial xlPasteAll, Transpose:=True
'.Columns("EE:HZ").Rows(cl).Copy
'Workbooks("TestCalcs_rev22.xls").Worksheets("init ial_information").Range("").
_
'PasteSpecial xlPasteAll, Transpose:=True


End With
Hide
End Sub

Private Sub btnCancel_Click()
Hide
End Sub