inserting rows - intresting

[kenguru]

Hello,

I'm using the following code:

ActiveSheet.Rows(ii + 1).EntireRow.Insert
ActiveSheet.Rows(ii + 1).NumberFormat = "@"

**insert some data on the "ii+1" line

and I'm having the following problem:
almost in all of the cases(90%) it works perfectly, but sometimes I saw that the rows aren't shifted down and because of this some data is overwritten by the new data.

Do you have any suggestion? I know, it is hard to imagine, and also hard to debug.

Thank You.
Kenguru

 

[CBasicAsslember]

Are you working from the bottom up?

 

[kenguru]

No I'm doing it from the top.
Kenguru.

 

[CBasicAsslember]

When you insert (or delete) a row all the rows below change location. Could this be your problem? I don't know without seeing the rest of your code.

 

[kenguru]

I know that they change location. The problem is that sometimes they are shifted done sometimes not.

The nearby code is:
a = ThisWorkbook.Sheets(sheet).Cells(ii, 1)
b = ThisWorkbook.Sheets(sheet).Cells(ii, 2)
c = ThisWorkbook.Sheets(sheet).Cells(i, 16)
d = ThisWorkbook.Sheets(sheet).Cells(i, 17)
e = ThisWorkbook.Sheets(sheet).Cells(i, 18)
f = ThisWorkbook.Sheets(sheet).Cells(i, 19)
g = ThisWorkbook.Sheets(sheet).Cells(i, 20)
h = ThisWorkbook.Sheets(sheet).Cells(i, 21)
v = ThisWorkbook.Sheets(sheet).Cells(i, 22)
ActiveSheet.Rows(ii + 1).EntireRow.Insert
ActiveSheet.Rows(ii + 1).NumberFormat = "@"

ThisWorkbook.Sheets(sheet).Cells(ii + 1, 1) = a
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 2) = b
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 3) = c
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 4) = d
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 5) = e
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 6) = f
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 7) = g
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 8) = h
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 9) = v

 

[CBasicAsslember]

I can't tell much from the code you posted because I don't know what the values are for i or ii. Step through the program and watch what happens.

 

[xlbo]

When deleting rows, always work from the bottom up - if you work from the top down, the row index gets all messed up and you get the kind of results you are seeing.

Generally happens when 2 rows next to each other need to be deleted. Always dlete from the bottom up. Full stop.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kenguru]

But I'm not deleting rows, i'm inserting rows.
Kenguru

 

[xlbo]

does the same thing to the indexes.

Inserting is the same as deleting in terms of the effect it has on worksheet objects like rows and columns

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question