macro to insert new row when number changes in column A

[Swaroon]

Hi,

I got this code from Norie earlier today, it inserts a row and copies the row above, works great, thanks.

But, I now need to delete one cell's data in each of the copied rows, the cell to delete is always going to be in column Q and will always be in the copied rows.

I tried to incorporate into Norie's code, a SendKeys function deleting the data 17 columns out to the right but it wouldn't work, any ideas ?

Thanks for your help, hope it makes sense
Steve


Sub InsertAndCopy()
Dim rng As Range

Set rng = Range("A1")

While rng.Value <> ""
If rng.Value <> rng.Offset(1).Value Then
rng.Offset(1).EntireRow.Insert
rng.EntireRow.Copy rng.Offset(1)
Set rng = rng.Offset(1)
End If
Set rng = rng.Offset(1)
Wend

End Sub

 

[Norie]

Steve

What is it you actually want to achieve?

If the problem with the code I posted is that it copies the entirerow that can easily be sorted, all you would need to do would be to remove EntireRow from the code.

By thew way why do you want to even try using SendKeys?

PS It's normally better to continue in the original thread.

 

[Swaroon]

Hi Norie,
Thanks for getting back on this.

What I have is a spreadsheet that with multiple data tabs, the end result is 3 tabs that will be saved as 3 individual csv files for uploading into an acconting system.

One of the tabs consists of lines of data, I can have say 4 lines of data which all have the column A reference of 1, 3 lines of data with ref 2, etc.

For the upload, we need a header line created, so 4 lines becomes 5, 2 becomes 3 etc.

The only change to the (new row) header line is that the serial number in column Q needs to be deleted.

The code you provided works fine for the new row, once the serial number is deleted I can attach additional code to sort the data so that the new line appears at the top, what I can't figure out is how to delete the serial number in column Q on the new line using the code you provided.

So, ideally, the code should add a new row at the top of each batch of row numbers in column A, then delete the serial number in column Q on the newly created line.

1 (New line) column Q serial number deleted
1 serial no 123
1 223
1 333
2 (New line) column Q serial number deleted
2 serial no 334
2 serial no 444
3 (New line) column Q serial number deleted
3
3
3
etc

If it's a problem getting the new row at the top, I can sort the data.

thanks for your help,

Steve