PDA

View Full Version : Quick VBA Rows Question



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

Grasor
04-15-2015, 05:14 PM
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:

JKwan
04-16-2015, 07:05 AM
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
04-16-2015, 08:15 AM
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
04-16-2015, 10:31 AM
Good point!