Quick VBA Rows Question

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:

Code:

---

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

Disregard! Earlier in my code I had Dim'd the value I was trying to use as "Counter" not "Count"

I swear...that was driving me crazy!:banghead:

you may want to put the below line onto your module, this will force all variables to be defined. This will get rid of the problem that you encountered

Code:

---

Option Explicit

---

Suggestion: count is a property of many objects in vba,
you might want to alter your choice for the variable name to cnt or aCount etc.
for example

Code:

---

lr = Cells(Rows.Count, "A").End(xlUp).Row

---

this is frequently used to find the last used cell in column A
count is a property of rows (and columns, and others)
hope this helps you on your path, and happy coding :)
-mark

Good point!