Quick VBA Rows Question

[Grasor]

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

[Grasor]

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!

[JKwan]

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

Option Explicit

[mperrah]

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

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

[JKwan]

Good point!