Macro to drag cells in colmn A-L down x rows and clean data?

June 3, 2016 at 07:17:46
Specs: Windows 7
I am looking for code ideas on how to write a macro to do the following. I have code for a button to add x rows beneath row y and now I want to format the newly added rows.

1) Drag down cells for columns A-L from row y into the newly added rows, copy cells do not fill series so Column B stays constant. I am trying to keep the formulas that are in the cells but not the values. The formulas are vlookup formulas pulling data from a separate tab. Column C contains the unique value that is user input. Columns A, E, F, G, H, I, K calculate based off value in Column C. Columns L, and J are user entered commentary for the rows.Column B is a constant. Its a production schedule.

2) After dragging down the cells then I would like to delete the values in Columns C, L, and J. The format calculated columns will update automatically when Column C is changed.

3) The third step is to assign a unique lot number in Column J to each row which consists of DDMMYYAAABB incrementing BB five values up starting at 01, 05, 10, 15 etc. to keep unique values for each row. AAA is a three digit constant.

Please share any ideas or pointers you might have. Thanks.


See More: Macro to drag cells in colmn A-L down x rows and clean data?

Report •


#1
June 3, 2016 at 07:37:21
1) To copy the cells down we could use the Autofill property this will copy the formula from the previous cell down to the newly added cells.

2) Do you want to delete ALL the values in columns C, L and J?

3) if I understand this correctly this shouldn't be too difficult

Will have to have a go next week now, but there are other members here that may be able to help before then.


Report •

#2
June 3, 2016 at 09:15:25
Hi, Yes, I would like to delete the values in C, L, and J within the newly added and auto-filled rows. The rows above should keep their original values. Let me know if any other questions. Thanks so much for the help!

Report •

#3
June 6, 2016 at 01:48:20
Cab you try this code please, this is the code for inserting the number of rows, as per my post in your previous thread, it will also copy the formulas down

please try and let me know if this is how it is meant to work and I will try to do the remaining

Sub InsertXRows()
    
    Dim xRows As Long
    xRows = InputBox("How many rows shall we add?", "Add rows")
    
    If IsNumeric(xRows) Then
        For i = 1 To xRows
            ActiveCell.Offset(1).EntireRow.Insert
            
            For v = 1 To 11
                ActiveCell.Offset(0, v).AutoFill ActiveCell.Offset(0, v).Resize(xRows)
            Next v
        Next i
    End If
    
End Sub

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
June 6, 2016 at 07:26:44
Getting this error:

Run time error '1004':

To do this, all the merged cells need to be the same size.

At this line:

ActiveCell.Offset(0, v).AutoFill ActiveCell.Offset(0, v).Resize(xRows)


Report •

#5
June 6, 2016 at 07:30:46
Can you hit debug and then paste the line of code that is highlighted please

Report •

#6
June 6, 2016 at 07:41:16
Sure, its also in the message above:

ActiveCell.Offset(0, v).AutoFill ActiveCell.Offset(0, v).Resize(xRows)


Report •

#7
June 6, 2016 at 07:47:02
oops sorry I totally missed that part of your post!!!!
Not sure what is happening it works perfectly for me, the only thing I can suggest is perhaps sending me your workbook removing all sensitive data?

if yes them PM me and I will send you the email address to send it to. Other than that I have no idea why this would happen..


Report •

#8
June 8, 2016 at 00:20:14
Iv tested the code with merged cells too and it doesn't seem to throw any kind of error for me.

Report •

#9
June 9, 2016 at 07:46:12
Sure I could send you the sheet. How do I PM you? Not seeing how to do individual chats on this site...

Report •

#10
June 9, 2016 at 08:31:42
If you click on my name 'AlwaysWillingToLearn' it will take you to my profile page, from there you will see my name again in blue top left, click on that and a new page will load where you can send me a PM

Edit: just send you a PM

message edited by AlwaysWillingToLearn


Report •

#11
June 13, 2016 at 00:21:16
Iv tested this on all sheets of your workbook and I have no problems with it, creating the number of rows you input and copying the data/formulas down.

Unless you specify the exact sheet that you run this code on, and tell us exactly what you do before you run the code, it will be very difficult for us to figure it out. As I said, I have run this successfully on all your worksheets.

my assumption is that, before you cleaned out your workbook to send to me, you had some data in the sheets, it may be possible that the format of the data or cells could be causing the issue but I cannot be certain as I don't see the data that you see..


Report •

#12
June 13, 2016 at 08:38:08
Thank you! I think I had a merged cell somewhere in the original sheet that was causing issues. I played around with it and was getting some errors at first in certain rows of the schedule but it is now working well. I think it had something to do with the fact that the black room header rows are a merged cell.

One minor issue, Column A does not appear to be copying the formula down from the row above like the other columns.

Also any ideas that you may have on the next two steps of the code would be super helpful...Really appreciate the help so far.

From Above:

2) After dragging down the cells then I would like to delete the values in Columns C, L, and J. The format calculated columns will update automatically when Column C is changed.

3) The third step is to assign a unique lot number in Column J to each row which consists of DDMMYYAAABB incrementing BB five values up starting at 01, 05, 10, 15 etc. to keep unique values for each row. AAA is a three digit constant.

Sorry for the slow responses last week due to travelling.


Report •

#13
June 15, 2016 at 01:37:44
This code will now copy column A down too. It will also clear columns C, J and L, do these columns have headings? this code will clear all the data in these columns including headings, if you want to keep the headings then you can change the number 1 to 2 in the following line

Range(Cells(<b>1</b>, 3), Cells(Rows.Count, 3)).ClearContents

I have also attempted the unique number part, you will need to test and let us know if this is what you wanted.

Sub InsertXRows()
    Dim bcell As Range
    Dim xRows As Long
    Dim iNum As Long

    xRows = InputBox("How many rows shall we add?", "Add rows")
    iNum = 5

    If IsNumeric(xRows) Then
        For i = 1 To xRows
            ActiveCell.Offset(1).EntireRow.Insert

            For v = 0 To 11
                ActiveCell.Offset(0, v).AutoFill ActiveCell.Offset(0, v).Resize(xRows)
            Next v
        Next i
    End If

    Range(Cells(1, 3), Cells(Rows.Count, 3)).ClearContents
    Range(Cells(1, 10), Cells(Rows.Count, 10)).ClearContents
    Range(Cells(1, 12), Cells(Rows.Count, 12)).ClearContents
    
    Columns(10).NumberFormat = "0"
    
    For Each bcell In Range("i1", Range("i" & Rows.Count).End(xlUp))
        
        bcell.Offset(0, 1).Value = Str(Format(Now(), "ddmmyyyy")) & "123" & Format(iNum, "00")
        
        iNum = iNum + 5

    Next bcell
    
End Sub


Report •

#14
June 15, 2016 at 06:48:43
Hi There! So this works to the way perfectly for the way I wrote it initially but it I didn't specify clearly enough about the column values and lot numbers. Basically Steps 2 and 3 should only happen to the column values in the newly added rows. Thinking that it would use some coding similar to the incrementing i and v for the row insertion and copying.

The values in those columns above and beneath the new rows should remain the same. Here is how it should have read:

2) After dragging down the cells then I would like to delete the values in Columns C, L, and J IN THE NEWLY ADDED ROWS, THE ROWS ABOVE AND BELOW THE NEWLY ADDED ROWS SHOULD REMAIN THE SAME. The format calculated columns will update automatically when Column C is changed.

3) The third step is to assign a unique lot number in Column J to each OF THE NEWLY ADDED rows which consists of DDMMYAAABB incrementing BB five values up starting at 01, 05, 10, 15 etc. to keep unique values for each row. AAA is a three digit constant.

The logic with the iNum incrementing makes sense. The lot code would ideally have not today's date but the start date from Column A. Also date format would be DDMMY. The other tricky piece is that the first two digit sub-lot at the end would be 01 instead of 00. Not sure if this poses an issue as the first increment would be up by 4 instead of 5. Also not sure if there is functionality to allow for variation across AAA value which has five possible options per each section on the schedule sheet. This could be specified in a hidden column value in each row and then maybe it could be grabbed while calculating? This step can also be user input if the coding is tricky.

Thanks again for your help with this.This is super helpful for our team. Let me know if you have any further ideas as your time permits.


Report •

#15
June 15, 2016 at 07:24:24
Will work on it as and when I get some time, should be able to make some of the changes very easily its not that difficult. I don't understand everything you are asking completely, may be my understanding, but what we will do is, update the code, you test it and let me know what needs modifying.

Report •

#16
June 15, 2016 at 07:29:57
Ok, feel free to reach out by private message or email if you need any points that you are not clear about.

Report •

#17
June 15, 2016 at 07:40:12
Have a little play with this one

Sub InsertXRows()
    Dim bcell As Range
    Dim xRows As Long
    Dim iNum As Long

    xRows = InputBox("How many rows shall we add?", "Add rows")
    iNum = 5

    If IsNumeric(xRows) Then
        For i = 1 To xRows
            ActiveCell.Offset(1).EntireRow.Insert

            For v = 0 To 11
                ActiveCell.Offset(0, v).AutoFill ActiveCell.Offset(0, v).Resize(xRows)
            Next v
        Next i
    End If

Range(Cells(ActiveCell.Row + 1, 3), Cells(ActiveCell.Row + xRows, 3)).ClearContents
Range(Cells(ActiveCell.Row + 1, 10), Cells(ActiveCell.Row + xRows, 10)).ClearContents
Range(Cells(ActiveCell.Row + 1, 12), Cells(ActiveCell.Row + xRows, 12)).ClearContents
    
    Columns(10).NumberFormat = "0"

    For Each bcell In Range("i" & ActiveCell.Row + 1, Range("i" & ActiveCell.Row + xRows))

        bcell.Offset(0, 1).Value = Str(Format(Now(), "ddmmy")) & "123" & Format(iNum, "00")

        iNum = iNum + 5

    Next bcell
    
End Sub

message edited by AlwaysWillingToLearn


Report •

#18
June 21, 2016 at 07:33:25
Awesome! This works great for clearing out the rows. I tried pasting in the new Column D values and it filled in the remaining data.

The only piece not working is the lot number.

Inserting four rows today I get the following:

210617312305
210617312310
210617312315
210617312320

Should be mmddyCCC01, mmddyyCCC05, mmddyCCC10. The mmddy should be based on start date in row A and CCC is a constant value which I am thinking I would have to put in a hidden column in ROW C or M so it could be looked up to fill in the three digits in the lot. Not sure if adding a row would effect the macro? There are three 5 different constants that go with different sections of the schedule.

The other tricky part would be to add something to see if there is a lot number that is the same in the rows above, this would happen if you were inserting new jobs that had the same start date as rows above, which is possible. In this case it would have to start at the next sub-lot. For example, if there was a 0621612301 above then it would have to know to increment and go to 0621612305.

Like I was saying before, we may be able to just have this column delete out and then do user input. Just hoping I can get away from all that daily typing.



Report •

#19
June 21, 2016 at 07:38:09
Yup i didn't quite do anything with the lot numbers i was concentrating more on the copying down of cells and clearing of data in the newly added rows. i will work on the remaining tomorrow.


Just clarify some for me, in post #14 you say

3) The third step is to assign a unique lot number in Column J to each OF THE NEWLY ADDED rows which consists of DDMMYAAABB incrementing BB five values up starting at 01, 05, 10, 15 etc. to keep unique values for each row. AAA is a three digit constant.

you mention DDMMY

but today you say you want mmddy

which one is it?

message edited by AlwaysWillingToLearn


Report •

#20
June 21, 2016 at 08:12:08
Yeah, sorry for the confusion. MMDDY

Report •


Ask Question