Grasor
04-15-2015, 03:08 PM
Hi All,
I've been studying VBA for a few weeks and am now starting to build my first project to put some concepts into action. Currently I'm working with a ListBox and am trying to paste selected items into an Excel sheet and for each item beyond the first insert a row below the first and so on and so forth.
Problem is, I'm ending up with an extra line at the bottom and I need to delete it. Problem is, I can't seem to decipher the Rows().Delete method.
I'm using a counter to determine how many times I go through the Insert-Paste loop then use that variable to delete the last row.
The code snippet is as follows:
Dim Count As Integer
Dim xRow As Integer
xRow = 39
Count = 39
'PasteStart = "B39:N39"
For ListBox1Row = 0 To .ListCount - 1
If .Selected(ListBox1Row) = True Then
Range("PasteStart").Offset(rowOffset:=1).Insert
Cells(xRow, 1).Value = .List(ListBox1Row, 1)
If xRow <40 Then
xRow = xRow + 1
End If
Count = Count + 1
Next ListBox1Row
Rows(Count).Delete
Nothing happens when the Rows(Count).Delete method is executed. However, if I replace the variable with a static value it works fine.
I.e. Rows(40).Delete works
but
Count = 40
Rows(Count).Delete does not work.
Why is that?
Thanks
I've been studying VBA for a few weeks and am now starting to build my first project to put some concepts into action. Currently I'm working with a ListBox and am trying to paste selected items into an Excel sheet and for each item beyond the first insert a row below the first and so on and so forth.
Problem is, I'm ending up with an extra line at the bottom and I need to delete it. Problem is, I can't seem to decipher the Rows().Delete method.
I'm using a counter to determine how many times I go through the Insert-Paste loop then use that variable to delete the last row.
The code snippet is as follows:
Dim Count As Integer
Dim xRow As Integer
xRow = 39
Count = 39
'PasteStart = "B39:N39"
For ListBox1Row = 0 To .ListCount - 1
If .Selected(ListBox1Row) = True Then
Range("PasteStart").Offset(rowOffset:=1).Insert
Cells(xRow, 1).Value = .List(ListBox1Row, 1)
If xRow <40 Then
xRow = xRow + 1
End If
Count = Count + 1
Next ListBox1Row
Rows(Count).Delete
Nothing happens when the Rows(Count).Delete method is executed. However, if I replace the variable with a static value it works fine.
I.e. Rows(40).Delete works
but
Count = 40
Rows(Count).Delete does not work.
Why is that?
Thanks