macro to insert new row when number changes in column A

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 18, 2005
Messages
281
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
 

Forum statistics

Threads
1,181,720
Messages
5,931,668
Members
436,797
Latest member
fullmetalpogi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top