Solved Copy/delete rows based on user input

December 3, 2014 at 14:32:55
Specs: Windows 7
Hello all

At work we have a big issue that I have got to sort out and I hope some friendly person on this nice forum would like to help me with that. The task I need help with:

I have two sheets. Sheet1 and sheet2. In sheet1 I have a lot of rows that are unique. Those rows are at the moment copied to sheet2 by simple =sheet1!$A$2 reference and I would like to keep it that way for the first copy of each row. But I often need more copies of those rows and therefore I need to be able to specify in column AR ect. how many copies of that row that has to be created in sheet2. If I put in the number 4 in row 2 in column AR, then I would like the code to check how many copies of that row there currently is in sheet2. If there arent enough copies of that row, the code should insert new lines and copy row 2 to those new lines. If I then later on delete a row manually in sheet2 or state a new "number-of-copies"-number in sheet1 column AR, then the last inserted row(s) should be deleted, until the number of copies was the right.

I have searched this forum and by google for a solution that fits, but I can't find exactly the right code for this, and I havnt been able to modify the code that I have found.

Many thanks in advance for your time!

/Jacob


See More: Copy/delete rows based on user input

Report •


✔ Best Answer
December 16, 2014 at 13:08:50
It looks like you didn't make one of the other changes that needed to be made.

In my original code, the indicated instruction was copying the Rows from Sheet1 based on the Row that the scrRw variable was pointed to at the time:


'If Rows are needed, insert them
  If insRwNum > 0 Then
    For insRw = 1 To insRwNum
      Sheets(1).Rows(scrRw).EntireRow.Copy '<--------
      Sheets(2).Rows(findRw.Row + 1).Insert
    Next
  End If

You changed the code to look at Sheet2, but you didn't change the variable that indicated what Row the code should be copying:

'If Rows are needed, insert them
  If insRwNum > 0 Then
    For insRw = 1 To insRwNum
      Sheets(2).Rows(scrRw).EntireRow.Copy '<-- scrRw needs to be findRw.Row
      Sheets(2).Rows(findRw.Row + 1).Insert
    Next
  End If

Therefore the code was using the row number from Sheet1, not the row number of the value it found on Sheet2. It was copying the values from Sheet2 to Sheet2 like you wanted, but it was copying the values from the wrong Rows. That explains both the messed up order and the wrong number of rows.

This code seems to work for me:

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "H") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(findRw.Row).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

As far as preventing a 0 from causing problems, there are a couple of things that can be done. Deleting a Row down to 0 is not the problem. The problem is trying to find a Row doesn't exist the next time the code is run.

We can capture the error that occurs when a value from Sheet1 is not found on Sheet2 and do "something" when that happens. You would need to tell me what you want done, e.g. Add the Row on Sheet2 (where?), pop up a message box, etc.

Another option is to use Data Validation on Column H and not allow a 0 to be entered. As a matter of fact, there should be Data Validation on the column to ensure that only whole numbers are entered. If a user enters e.g. a text string, the code will fail. Setting Data Validation to Whole Numbers > 0 solves both issues.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
December 3, 2014 at 14:53:38
The first thing that caught my eye was this:

"I need to be able to specify in column AR ect."

It's the ect. that concerns me. If the "number of rows" value will only be placed in Column AR that simplifies things. If the value might be in other columns, perhaps a certain column for certain rows, but another column for other rows, then things get a bit more complicated since the code will need to know where to find that value.

The next thing that is not clear is this: "In sheet1 I have a lot of rows that are unique. "

What do you mean by "unique"? The reason I ask is because the code will need to know how many of that row exists on the Sheet2 before it can determine how many copies to insert. Is there a specific column that can be used as the value for the code to find in each row in order to count how many copies of a given row exist? A cell by cell search of every row to determine if it matches another row on Sheet 2 is probably not a technique we want to employ.

Finally (at least for now) there's this:

"If I then later on delete a row manually in sheet2 or state a new "number-of-copies"-number in sheet1 column AR, then the last inserted row(s) should be deleted, until the number of copies was the right."

Stating a new number of copies in a specific location would work. Deleting a row and then expecting the code to figure out what row was deleted would be difficult if not impossible. The code would need something to find (or not find) but it needs to have something to search on. A row that has been deleted leaves nothing for the code to work with.

I think we need a little more clarification on how you want this to work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 3, 2014 at 20:47:44
Thanks for the quick reply.

column AR was just a suggestion to what column to use for then "number of copies" values. It was just to point out, that I would like to be able to change this column to another column later on (manually in the code if needed), if I needed to insert more columns before that. The values "number of copies" will always be in the same column.

To clearify the unique part, it is just to say, that column B contains numbers that are unique to that line in sheet1, its like a serial number. This unique number will always be in that column. It was just to say, that column C and so on could contain numbers that are the same as the previous line and therefore they can not be used as a references,

I am not totally sure about what you mean about the part about the deleting rows things..
Eh, its just if I by mistake put in the number 4 in the number-of-copies column in sheet1, 4 copies will be inserted in sheet2. I only needed lets say 2 copies. Then I need those 2 copies that are to much in sheet2 to be deleted, starting by the last rows for that specific serial number.

But it would also be nice if I just could delete the row manually. I am just not sure if I delete the row manually in sheet2, would the number-of-copies number for that row on sheet1 be changed or will the code automaticly insert the deleted row again, because the number-of-copies value is in conflict with the current counts of that row in sheet2?? I wouldnt care if I needed a column more, for this to be able to function. This code about the manually deleting those rows that are too much is a feature, that could be skipped, if it is to hard to make. Its just 20 people are working with this worksheet at the moment and at the moment we all delete those rows manually and I could think that it would be hard for them to drop the possibility to delete the rows manually instead of going to sheet1 and specify a new number of copies needed.


Report •

#3
December 4, 2014 at 08:58:10
Do you have any suggestion to a solution?

Report •

Related Solutions

#4
December 4, 2014 at 10:04:58
Please be patient. We are all volunteers on this site with real jobs and real lives. We answer questions as time allows.

Before we can post a solution we actually have to come up with that solution and test it. That means setting up spreadsheets that we think look like your spreadsheet, then writing the code to meet your requirements and then testing it. I have yet to find a way to have VBA code write itself and test itself.

The deletion of rows with not a minor task and may take some time.

I will work on something and post my results when I feel I have something worth posting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 4, 2014 at 11:46:23
Sorry if I sounded to demanding, I am very greatfull for any help you or others can provide on this task and I know it is a complex peace of code that is needed. I will try to be more patient and wait and see if a solution is posted.

Report •

#6
December 4, 2014 at 12:03:56
A question:

What are your users doing on Sheet2, the sheet with the multiple rows?

A quick and dirty solution to your task would be to simply clear and then re-populate Sheet 2 each time the macro is run. That would ensure that Sheet 2 always contained the exact number of rows specified in Column AR of Sheet1.

Obviously if the Rows on Sheet2 are being edited after they are copied, then this solution won't work.

Is there anything you can tell us about how Sheet2 is used that might help simplify this "synchronization" task.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
December 4, 2014 at 20:15:19
Yes, and thats why I think its tricky. Normally the workflow is like this: we type the basic data in each row of sheet1 and then we go to sheet2 and put in the rest of the data, so yes, we do edit sheet2, so it is very important, that the lines that is being removed always are the last lines inserted. At the moment, we are putting all the data we can in sheet1, then we go to sheet2 and insert a new row underneath the row that has been copied from sheet1, and manually copy that row to the newly created row.

Is it only the delete-part of the code, that is tricky? Because it is a lot easier to delete rows manually, than it is to insert a new line and copy the above row into that new row. I think we could live with a solution that didnt delete those rows, but obvious it would be a help to have this feature.


Report •

#8
December 7, 2014 at 21:12:22
Sorry it's taken so long...busy weekend.

I think this code does what you want. I suggest that you test it in a backup copy of your workbook since macros can not be undone.

I am assuming that you have Header Rows in Sheet 1 and 2 with your data starting in Row 2.

I don't know how much you know about writing VBA code - specifically VBA debugging techniques - so I'll offer the following link just to be safe. The more debugging you can do on your own, the better. You'll learn more and you'll be able to modify the code on your own when required.

http://www.computing.net/howtos/sho...

Let me know how this works for you.

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"))
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "AR") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(1).Rows(srcRw).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
December 8, 2014 at 10:29:13
Hello again DerbyDad, thanks for det code. I get an error somehow. Altho I have tried to look at your link about the debugging errors guide, I havnt been able to figure out what this error means: Run-time error '91': Object variable or With block variable not set and when I press the debug button it highlights this line:

Sheets(2).Rows(findRw.Row + 1).Insert 

OKAY, I found the solution. It was my mistake. I had the "unique" numbers in the wrong column. That produced the error.

Thank you very much for your work on this DerbyDad!

Now I only need to ask for a solution for this and I am ready to put this into our worksheet at work. A lot of people will be glad to see this improvement:
How can I make it run automaticly? Either if the values of the "number of copies" changes or better than that, if it is run all the time and keeps checking if the "number of copies" values change.

Is there maybe also a way to change the "number of copies" values for each line in sheet1 if I manually delete the row on sheet2?

message edited by JacobJ


Report •

#10
December 8, 2014 at 12:48:57
Since I don't have a copy of your work, I'm not 100% sure why you are getting that error, but I have a suggestion that might help.

In the 'Search Sheet 2 Column B for values, count each occurrence section,

replace this line:

        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"))

with this line:

        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)

As far as the placement of the code...

1 - Open the Visual Basic Editor
2 - Choose Insert...Module
3 - Paste the code into that window

Let me know how that works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
December 8, 2014 at 22:31:07
Hello again and thanks for the quick reply.

I have found out this about the error: It only comes if there is no match in sheet2 for a cell value in sheet1 column B. Somehow it needs that information to put in the row. The new line of code didnt cure that. Any chance that it could just insert the row or rows if it didnt find a match in sheet2, like a:

if (match) then copy row
else
Insert rows (that have no match)

But another problem is this:
It copies the rows from sheet1 to sheet2, it must copy the rows from sheet2 to sheet2, because the header lines in those two sheets arent the same and there are data in sheet2, that comes from other sheets.


Report •

#12
December 9, 2014 at 12:41:03
re: It only comes if there is no match in sheet2 for a cell value in sheet1 column B.

In your original post, you said:

In sheet1 I have a lot of rows that are unique. Those rows are at the moment copied to sheet2 by simple =sheet1!$A$2 reference and I would like to keep it that way for the first copy of each row.

That indicates, at least to me, that every row in Sheet1 has at least 1 copy in Sheet2.

But I often need more copies of those rows

"more copies" once again indicates, at least to me, that at least 1 copy already existed.

Then you said:

If I put in the number 4 in row 2 in column AR, then I would like the code to check how many copies of that row there currently is in sheet2. If there arent enough copies of that row, the code should insert new lines and copy row 2 to those new lines.

Note the words "copy row 2". Once again, that indicates, at least to me, that you wanted to copy the unique rows from Sheet1 to Sheet2. The odds are that the row to be copied will not Row 2 on Sheet2 so I don't know how I could have gotten any other impression.

What you are now asking for is considerably different than what you originally asked for. I'll need to go back and rewrite the code to see if your current requirements can be accomplished. I may have more questions as I move forward.

In fact, as I think about this, a question already comes to mind.

To once again quote your original post:

Those rows are at the moment copied to sheet2 by simple =sheet1!$A$2 reference and I would like to keep it that way for the first copy of each row.

Now you ask: Any chance that it could just insert the row or rows if it didnt find a match in sheet2...?

Which leads me to ask: You originally said that the first copy of a row is made up of formulas (=SHEET1!$A$2) and that you would like to keep it that way for the first copy of each row. Now it appears that simply inserting the row that didn't exist is enough. So which is it? Does the first copy of a row have to be made up of formulas (much harder) or can the first row simply be a copy of the values in the row copied from Sheet1?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#13
December 9, 2014 at 20:47:04
Okay I can see I havn't been all that clear about what rows I needed to be copied.

To take it from the beginnning;

In sheet1 I have a lot of rows that are unique. Those rows are at the moment copied to sheet2 by simple =sheet1!$A$2 reference and I would like to keep it that way for the first copy of each row.
That indicates, at least to me, that every row in Sheet1 had at least 1 copy in Sheet2.

This is how it should be. So we have understood each other on this part of the issue.

Now:

If I put in the number 4 in row 2 in column AR in sheet1, then I would like the code to check how many copies of the row with the unique number there currently is in sheet2. If there arent enough copies of that row, the code should insert new lines and copy row 2 of sheet2 to those new lines.

I have inserted where I could have been more specific with bold text.

Which leads me to ask: You originally said that the first copy of a row is made up of formulas (=SHEET1!$A$2) and that you would like to keep it that way for the first copy of each row. Now it appears that simply inserting the row that didn't exist is enough. So which is it? Does the first copy of a row have to be made up of formulas (much harder) or can the first row simply be a copy of the values in the row copied from Sheet1?

The reason why I need the formulas to be copied into the rows below the original row is that I sometimes need to change some values in sheet1 and that should automaticly be updated in those rows in sheet2 which the data-change is relevant to.

So as I see it, I need to have exact copies of the rows in sheet2 to be inserted below the original row, so if changes are made all rows no matter how the are created will be updated. Put in another way, I don't need only the values from the rows in sheet2 to be copied, because that wouldnt make any afterward editing possible.

I should have told you at the beginning, that data from the rows in sheet1 is put into the rows in sheet2 by simple =sheet1!$A$2 and that I want to be able to specify in sheet1 column AR, how many exact copies of those individual rows in sheet2 that I needed to be copied and inserted below the now existing copy.

I am very sorry that I havn't been all that clear about which rows should be copied, please forgive me. I am from Denmark, so I may lack the language skills to express myself as clear as needed, but I will try to do better from here.


Report •

#14
December 10, 2014 at 04:55:27
Something still doesn't make sense.

In Response #11 you said:

I have found out this about the error: It only comes if there is no match in sheet2 for a cell value in sheet1 column B.

But we've also had this exchange:

In #12 I said:

That indicates, at least to me, that every row in Sheet1 has at least 1 copy in Sheet2.

To which you replied in #13:

This is how it should be. So we have understood each other on this part of the issue.

How can there be "no match in sheet2 for a cell value in sheet1 column B" if we've agreed that every row in Sheet1 has at least 1 copy in Sheet2?

There must be something about your process that either I am missing or you haven't told me about.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
December 10, 2014 at 11:45:44
I posted the error as soon as I encountered it and had made my debugging. I then later figured out, that I had my "unique" numbers in the wrong column (I have made a simple test file where I am testing the concept before implementing it in the worksheet we have at work).

So basicly what I am trying to tell is that I made an error and that the code worked fine.

But as I can see, we have misunderstood each other, because the rows that needs to be copied are the rows in sheet2, based on the number-of-copies value specified in sheet1 column AR. See #13 for a detailed (and hopefully specific) description of the requirements the code needs to meet.


Report •

#16
December 10, 2014 at 11:58:26
ALMOST THERE!!!

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "H") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(srcRw).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

This code does what I want, I just modified it slightly by referencing sheet2 where sheet1 was named and tried it out.

The only problem I now has with this, is that if I by accident type in 0, then it comes up with the error 91 again, because the code ofcause can not find the match on sheet2. Can I somehow prevent the users from entering a 0 in a cell?


Report •

#17
December 10, 2014 at 12:25:17
I have now tried to make the code fire automaticly when a cell in column H (previous AR) is changed.

I get a syntax error on this code and the first line is highlighted with yellow and the other line in the code below that is in bold is colored red.

<b>Private Sub Worksheet_Change(ByVal Target As Range)</b>
    If Intersect(Target, Me.Range("$H$1:$H$10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
    
    
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            <b>firstAddress = findRw.Address</b>
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "H") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(srcRw).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
    
    

    Application.EnableEvents = True
End Sub


Report •

#18
December 10, 2014 at 16:25:24
Just FYI...

You can't use any other formatting within the pre tags in this forum. Your attempts to bold lines within your code are fruitless. If you want to "highlight" a line, use something like a comment symbol, an arrow, etc.

e.g.

         If Not findRw Is Nothing Then
                firstAddress = findRw.Address '<---- look here ------
              Do
                   numRw = numRw + 1

That way the code can be pasted into the VBE and used without editing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
December 10, 2014 at 20:09:26
Okay, I will try to remember that, thanks.

Report •

#20
December 11, 2014 at 10:35:24
I have now played with the code and I have some problems. Here is my testsheet with the code.

http://www.filedropper.com/testsheet

First time you run the macro it all seems to work fine, but the second or maybe the third time it puts in a random number of copies and the rows are all mixed up. For what I know this could be a timing issue, but that is just from my limited knowledge about these kind of symptoms.


Update
I am more and more sure that it is a timing problem of somekind. The problem occurs sometimes when I use low number-of-copies to be inserted, but if I use big numbers its getting a mess and lots of times it just stops and not all rows are getting inserted.

message edited by JacobJ


Report •

#21
December 11, 2014 at 13:04:46
Unfortunately, as of this evening I will be travelling all weekend with no access to Excel to test anything. I won't be able to work on this until next week.

For now, you might try adding this line just below your EnableEvents instruction:

Application.ScreenUpdating = False

This will prevent the system from trying to display the inserted rows each time one is inserted. Perhaps that will solve the timing issue (if that is what it is) but as I've said I can't test anything for a few days.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#22
December 11, 2014 at 13:47:04
Thats totally fine, I will check your suggestion and return with the results. Talk to you next week.

Report •

#23
December 15, 2014 at 07:19:37
Okay I havnt been able to sort out my problems with this.

I went back to your original code, the code that is copying the rows from sheet1 to sheet2 and that works just as it should.

How can I modify the code, so that it will copy the rows from sheet2 to sheet2 based on the number-of-copies in column AR in sheet1? I thought a simple change of the copy row line in your code could do it, but that somehow doesnt work...


Report •

#24
December 16, 2014 at 11:52:43
Ok, there are at least a couple of issues:

It appears that you changed this line:

'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
                     ^
                     ^
to

'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
                     ^
                     ^

That won't work. The purpose of that instruction is to determine the last row of the range that contains the original set of values in Column B on Sheet1. That is the number that is used to loop through the original set of values and look up the value in Column H. That instruction must refer to Sheet1 or the lastSrcRw variable will be set to bottom of the list in Column B of Sheet2. That is proabably why your code is running forever.

As far as the code messing up after the first run, I am going to have to work on that. The problem seems to occur when you increase the number of some rows and decrease the number of others. When you do that, the counters get all messed up and the code is not looking at the correct rows any more.

I'll work on it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#25
December 16, 2014 at 13:08:50
✔ Best Answer
It looks like you didn't make one of the other changes that needed to be made.

In my original code, the indicated instruction was copying the Rows from Sheet1 based on the Row that the scrRw variable was pointed to at the time:


'If Rows are needed, insert them
  If insRwNum > 0 Then
    For insRw = 1 To insRwNum
      Sheets(1).Rows(scrRw).EntireRow.Copy '<--------
      Sheets(2).Rows(findRw.Row + 1).Insert
    Next
  End If

You changed the code to look at Sheet2, but you didn't change the variable that indicated what Row the code should be copying:

'If Rows are needed, insert them
  If insRwNum > 0 Then
    For insRw = 1 To insRwNum
      Sheets(2).Rows(scrRw).EntireRow.Copy '<-- scrRw needs to be findRw.Row
      Sheets(2).Rows(findRw.Row + 1).Insert
    Next
  End If

Therefore the code was using the row number from Sheet1, not the row number of the value it found on Sheet2. It was copying the values from Sheet2 to Sheet2 like you wanted, but it was copying the values from the wrong Rows. That explains both the messed up order and the wrong number of rows.

This code seems to work for me:

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "H") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(findRw.Row).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

As far as preventing a 0 from causing problems, there are a couple of things that can be done. Deleting a Row down to 0 is not the problem. The problem is trying to find a Row doesn't exist the next time the code is run.

We can capture the error that occurs when a value from Sheet1 is not found on Sheet2 and do "something" when that happens. You would need to tell me what you want done, e.g. Add the Row on Sheet2 (where?), pop up a message box, etc.

Another option is to use Data Validation on Column H and not allow a 0 to be entered. As a matter of fact, there should be Data Validation on the column to ensure that only whole numbers are entered. If a user enters e.g. a text string, the code will fail. Setting Data Validation to Whole Numbers > 0 solves both issues.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#26
December 17, 2014 at 08:05:43
I see, thats why it didnt work with my modifications.

I have a problem with the new code tho. If I put the number 3 in H2 in sheet1, then row 3 in sheet2 gets copied 3 times and not row 2 as it should.

How can that be? Somehow I think there should be a -1 or +1 somewhere in the code, but I can't seem to find it.

As to the data validation in column H and restrict the users from putting in anything other than a whole number and a number > 0, is the best solution.

UPDATE:
Okay, I have found the problem (again, it was myself that was to blame). The problem was that some of the data in the columns were not absolute in reference. But now I at least know, that everything in the rows has to be referenced as absolute for it to work. (Can't remember if they aren't already all referenced as absolute, but I need to check up on that). I can't see if this will get me any problems in the long run, but maybe you can clearify that for me. If there is only text or a number in a cell in a row, will that text or number always be copied exactly as it is down to the next row with this code?

The code works just as it should and I will like to thank you very much for your very big help with this. I will build it into the worksheet at work on friday and see how it all turns out.

I have a question that is somehow related to this, shall I post that in a new thread or just in this one? Its about inserting and deleting rows based on a single number.

message edited by JacobJ


Report •

#27
December 17, 2014 at 10:48:33
Any suggestions to how I can handle the manual deleting of the rows in sheet2, so that the number-of-copies value changes to reflect the actual number of times that row is present in sheet2?

Report •

#28
December 18, 2014 at 03:55:38
I'm not quite sure why you marked this thread as solved when you have more questions.

I don't know what you are asking about in #26, so I can't tell you if the question should be in it's own thread.

As for #27, I don't know why you would be manually deleting rows in sheet 2 if we've given the user the ability to delete rows by changing the value in Sheet 1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#29
December 18, 2014 at 13:13:12
I havent marked this thread as solved! That must be some admin or something???

#27: For me and you the right way to do it is to go into sheet1 and change the number, but for the people at my work there can't be to many changes from how they do it now, to the way I want them to do it. So the ability to delete a row manually in sheet2 (and automaticly change the number in sheet1) would be a great way to be more adaptive to how they work now.

As for #26, I will try to post my question here and see how it works out. The question will come tomorrow, because I am a little bit busy today.


Report •

#30
December 19, 2014 at 09:14:33
re: #27: For me and you the right way to do it is to go into sheet1 and change the number, but for the people at my work there can't be to many changes from how they do it now, to the way I want them to do it

Bill Polian (NFL General Manager extraordinaire) once said "If you listen to the fans eventually you'll find yourself sitting with them."

The same can be said for users. User input is extremely important and should never be discounted, but there are times when catering to the users can be a detriment to the overall project. Instead of catering to the users in this instance, I would gently nudge them towards the "better way". If we add code to change the number on Sheet1 when a row is deleted on Sheet 2, then we need to add code to change the number on Sheet1 when a row is inserted. What if they try to Cut/Copy and Paste a row, either on purpose or by accident? Well we better have to code to deal with that situation also. Things are getting a bit complicated aren't they?

You were willing to use Data Validation to prevent the users from inputting a zero on Sheet1, so why not implement a method to prevent the users from manually inserting or deleting rows on Sheet2?

On the Home ribbon choose Format...Protect Sheet. The check every box except Insert Rows and Delete Rows. (You can do the same for columns if you think that is necessary.) Add a password if you'd like or take your chances that the users won't try to change those settings. This will prevent the users from Deleting or Inserting rows on Sheet2.

If you want, you can nudge the user back to Sheet1 to change the number with the following code, but just keep in mind that the MsgBox will appear each time the right mouse button is pressed on Sheet2, so that might get annoying.

Right-Click the sheet tab for Sheet2 and choose View Code. Paste the following code into the pane that opens so that the users will be aware that they cannot Delete or Insert Rows on Sheet2. All other right click functions will be allowed as soon as the MsgBox is cleared.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
                                       Cancel As Boolean)
    MsgBox "Deletion and Insertion of Rows is " & _
           "disabled in this Worksheet." _
            & vbCrLf & vbCrLf & _
           "Please change the value in Column H of " & _
           "Sheet1 to your desired number of Rows." _
           & vbCrLf & vbCrLf & _
           "Thank you for your understanding."
End Sub

Oh...BTW...If you are protecting the sheet, you'd better protect the VBA project so that users can't view/alter the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#31
December 19, 2014 at 09:40:20
Thanks for that suggestion. I can see your point. I didnt know that you could protect the sheet from alteration. Can the users input data as normal??? Can the formulas be overwritten by text manually put into the cells? Because that I need to allow them in some places.

As for #26:

I have a datasheet where I put general data that relates to all customers and in that sheet I also define different groups.

The data from the datasheet gets transfered to the sheet1 we have talked about in this thread. It would be very fine to have the option to specify how many rows that was needed in each group in sheet1. Let me take an example:

In the datasheet I type in the following in group 1:

Customer group name: International orders
Customer starting year: 2002
Customer order status: Active
Number of customers in group: 50

in group 2 the data could be this:

Customer group name: National orders
Customer starting year: 2001
Customer order status: Active
Number of customers in group: 20

and so on. I have 6 groups at the moment.

At the moment each groups data is put into sheet1 by simple reference, like =datasheet!B2 and this is then filled in down, by simple push and drag down. Lastly all the those references are made absolute with a macro ive found.

What I am looking for is a way either to offset where the group 2 data starts to be filled in or just to insert some empty rows corresponding with the numbers defined for each group. I think the first approach is the best solution, because then all the formulas and the absolute references are keept.

Any suggestions to this?


Report •

#32
December 19, 2014 at 10:19:08
re: I didn't know that you could protect the sheet from alteration. Can the users input data as normal??? Can the formulas be overwritten by text manually put into the cells?

All I can suggest is that you read the options available in the Protect Sheet dialog box, choose what you want to allow the users to do and then test it to see what impact that has on your process.

As for #26, once again I'm totally confused.

Up until now I was picturing Sheet1 as a bunch of single line entries, with the values to be search for in Column B, the number of required rows in Column H and probably a bunch of other data in each row. The code we are working with scans Column B and matches the number of Sheet2 rows with the value in Column H. Basically, it's pretty simple.

Now you are talking about 6 sets of "group data" that need to be "offset" (from where?) in Sheet1. I have no clue how this "offset" will impact the macro that simply scans Column B and counts the rows in Sheet2. If we start moving data around and mess up what's in Sheet1!Column B then there is a strong possibility that the code will no longer work.

You'll need to clarify this "group data" issue, once again keeping in mind that I can't see your workbook from where I'm sitting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#33
December 19, 2014 at 11:02:49
One thing I need to clearify is that its like a template sheet. When we load the worksheet its totally free of any data.

The way I was thinking is to build it up in steps. You have 3 main sheets; datasheet, sheet1 and sheet2. In the datasheet the general data for all customers are put in and there you specify how many customers there are in each group. In sheet1 you will then have the number of rows you specify in the group data "box" on the datasheet as mentioned above. Now the only thing that is in sheet1 is a bunch of "empty" rows, meaning that there are only formulas present in the rows PLUS the general data that is transfered and filled in in a few columns.

I can't see how this new idea has anything to do with the copying of rows in sheet2? I understand that the code for this copy/insert/deleting is using the data from sheet1, but the data will first be there when you type it in and that is after you have specified the number of customers in each group.

If there are 50 customers in group 1, then the group 2 data should start getting filled in from the datasheet 50 rows below the first group 1 data.


Report •

#34
December 19, 2014 at 13:40:32
re: "I can't see how this new idea has anything to do with the copying of rows in sheet2?"

As I've said numerous times, keep in mind that I can't see your workbook from where I'm sitting. Now that you've explained the "group data" issue, it make more sense. Your introduction of the "group data" in #31 and your need to bring it into Sheet1, using "offsets" etc. is what led me down the path of changes being made to Sheet1 that might possibly impact the code.

I can't count how many times I've spent hours going back and forth with members of this forum, trying to gather requirements, then writing and tweaking code that eventually meets those requirements, only to have the person say "OK, now that we got that worked out, I also need it to do this". In other words, they only supplied part of the requirements, not realizing that the additional requirements would either result in an efficient bolt-on to the original solution, or, worse yet, a complete re-work.

Now that it appears this initial build of Sheet1 by pulling information from another sheet shouldn't impact the code offered as a solution the original question asked in this thread, I'm going to change the game a little.

Since the question asked in #31 & clarified in #33 is not related to the original question in this thread, I'm going to ask that you post the question in a new thread with a relevant subject line. I will also add that I'm still not clear how the data is laid out in your "datasheet" or how it is supposed to get into Sheet1 so that the InsertDelete_Rows macro can be run against it. The "offset" thing and number of customer lines is still not clear to me.

In order to avoid my having to ask for clarification in the new thread, please be as clear and detailed with the requirements of the new task when you start your new thread.

Since it appears that we have resolved the Insert/Delete Rows issues, I request that you now mark #25 as the best answer since that post appears to contain the most up-to-date code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#35
January 6, 2015 at 00:55:24
I have just succesfully build in your code in my worksheet at Work and it Works just as it should. Very nice!

It does tho have some delay to it. Maybe it is my computer that is to slow, but is there anything I can do to speed up the process?

At first it took very long time for it to run through, but figured out that the problem was due to the fact that there were numbers in all 1000 rows, which the code checked (000000 was the number all the way Down). I changed that with an if statement to check if the referenced cell was blank and that did the trick.

Now I can type in ect. the number 5 in the number-of-copies cell and change sheet and then I can see the lines getting copied Down one by one. So a delay of a half a second per row is what I have, but it would be nice to have a smaller delay.


Report •

#36
January 7, 2015 at 01:52:21
Okay now I have a bit of a problem. I have made data validation for the number-of-copies column, so that the cells only can contain the number 1 or more.

But the problem is, that if I delete the ID number the code has no ID to search for, so it doesnt know which rows to use. Can I somehow make the code skip the rows and leave them out of the equation if there is no ID number present?

I have been thinking about making some sort of predefined ID number if the above isnt a possibility. Then there would always be a fake ID number to Work with for the code, but that could lead to some confusion and I am not totally sure if that solves the problem.

ALSO: Is is possible to make the code skip a line if I define a value in a new column? That would be nessesary if there shouldnt be made a letter for that customer.

message edited by JacobJ


Report •

#37
January 7, 2015 at 09:32:07
Another big problem emerged after a whole day of work with this code.

If there are more than 9 rows of ID numbers the code fails to either duplicate the right amount of rows or it "deletes"/skippes some rows.

My modified version of your code is this:

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(1)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "A"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "F") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(findRw.Row).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

I have uploaded an example sheet. Try putting in the number 10 in the ID number coloumn after the ID number 9 and put 1 in the number of copies cell for that row and run the macro:

http://www.filedropper.com/examples...

The row with the ID number 2 disapears! How can that be? I guess its something really simple, but havnt been able to figure it out af 8 hours on this...


Report •

#38
January 8, 2015 at 11:37:18
Today I worked 7 hours more on this and its not getting any better.

When I have 9 rows with ID numbers and 3 copies of each row specified and run the macro it all works fine.

When I have 10 rows with ID numbers and 3 copies of each row and run the macro, 3 rows of row 3 to 10 is copied just as it should, but only 2 rows of row 2 and 1 row of row 1. If I specify 4 in the number of copies column, it copies 4 rows of row 3 to 10 just as it should, but only 3 rows of row 2 and still 1 row of row 1. The number of copies of row 2 is always 1 row less than specified if I have 10 ID numbers.

When I have 11 rows with ID numbers and 3 copies of each row specified in the column number-of-copies I get: 3 copies of row 4 to 11, NONE of row 3 and 2 copies of row 2 and still only 1 copy of row 1.

When I have 12 rows with ID numbers and 3 copies of each row specified I get this:
3 copies of row 6-12, NONE of row 4 and 5 and only 1 copy of row 1, 2 and 3.

When I have 13 rows with ID numbers and 3 copies of each row specified I get this:
3 copies of row 8-13, NONE of row 4, 5, 6 and 7 and only 1 copy of row 1, 2, and 3.

I can see a pattern in these results, but I am not able to tell where the problem is. When I run the code with a higher number of ID numbers (like 12 and 13) it looks like the code is adding all the rows as it should, but it then deletes them again. So maybe the issue is somewhere in the deleting part of the code.

Any suggestions to what I could try or what could be the problem here?


Report •

#39
January 8, 2015 at 13:22:07
I can not download your file at this time due to corporate restrictions...it will have to wait until I have time at home.

That said, how have you been testing the code? Are you just changing numbers and running the code or are you using the debugging techniques that can be found in the tutorial I linked to in Response #8?

If you are simply running the code and then looking at the results, it can be very hard to find the problem. However, if you are using break points and single stepping through the code as it runs through each section, watching your variables, etc. you might be able to see why it is not working. That's what I would do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#40
January 8, 2015 at 13:41:58
Okay. I have tried to step through the code and I have found a problem with the code marked with !!!!PROBLEM!!!!:

Sub InsertDelete_Rows()
'Determine Last Row with data in Sheet 1 Column B
  lastSrcRw = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Initialize Row counter
    numRw = 0
'Search Sheet 2 Column B for values, count each occurence
      With Sheets(2).Columns(2)
        Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)
         If Not findRw Is Nothing Then
            firstAddress = findRw.Address
              Do                                               <!!!!PROBLEM!!!!
               numRw = numRw + 1
               Set findRw = .FindNext(findRw)
              Loop While Not findRw Is Nothing And _
                    findRw.Address <> firstAddress
         End If
      End With
'Calculate how many rows need to be inserted
     insRwNum = Sheets(1).Cells(srcRw, "H") - numRw
'If Rows are needed, insert them
       If insRwNum > 0 Then
         For insRw = 1 To insRwNum
           Sheets(2).Rows(findRw.Row).EntireRow.Copy
           Sheets(2).Rows(findRw.Row + 1).Insert
         Next
       End If
'If there are too many Rows, delete them
       If insRwNum < 0 Then
''Determine last row with value to be deleted
        delRwNum = findRw.Row + numRw - 1
         For delRw = -1 To insRwNum Step -1
           Sheets(2).Rows(delRwNum).EntireRow.Delete
''Decrement Row number to be deleted
           delRwNum = delRwNum - 1
         Next
       End If
   Next
End Sub

I don't know why, but at a certain point this code does something weird:

Set findRw = .FindNext(findRw)

When i have 12 ID numbers and 1 number-of-copies specified it does this:

If I step through the code until the findRw is 2 and I run that single line above one more time, it goes to 12 (which is the number of rows findRw is in the first run-through).

I have a clue that it could have something to do with the FOR statement, that somehow runs empty of new lines to test, but I am by no means an expert on this, so that is like I said only what I suspect to be the problem.

message edited by JacobJ


Report •

#41
January 8, 2015 at 13:52:02
Funny you just mentioned that, because I was doing the debugging at the time of your reply. See post #40.

Report •

#42
January 8, 2015 at 18:03:40
As far as I can tell, this is the problem line:

Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), LookIn:=xlValues)

As explained at the following site, the Find feature within Excel and the .Find method in VBA are interconnected:

http://msdn.microsoft.com/en-us/lib...

I'll quote the relevant paragraph, but you should review the other information available at that site:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

My guess is that on your system the current setting for the LookAt argument is xlPart. The way to verify this would be to go into your spreadsheet, open the Find dialog box and see if the "Check entire cell contents" box is checked. My guess is that it is not.

What is happening in the case of adding a ID number 10 is that the code is finding a single instance of 10 on Sheet2, but 2 instances of 1 (1 and 10). This is because the code is not checking the entire cell contents as as single value (xlWhole) but is checking for partial values (xlPart).

When you add ID numbers 11 and 12, things get even worse because the code is now finding 4 instances of 1 (1, 10, 11 and 12), 2 instances of 2 (2 and 12), etc. Once the code starts adding rows when it shouldn't, the deletion section is going to get messed up as well.

The fix, as stated in the paragraph I quoted above, is to explicitly set that option in the code. I don't always do that, perhaps I should start to always do that in the future.

Try this:

Set findRw = .Find(Sheets(1).Cells(srcRw, "B"), _
              LookIn:=xlValues, LookAt:=xlWhole)

Once you run the code with that line, not only should the code work, but if you go back into the Find dialog box in Excel, you should find that the "Check entire cell contents" box is now checked. Even if it gets manually unchecked in the future, the code will change the check box it each time it is run because you have explicitly set it via the LookAt argument.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#43
January 8, 2015 at 20:32:25
For once again it looks like your totally right about how this all works. I did check the find dialog box and you were correct, the box with the match entire cell contents wasnt checked. I substituted the line of code and it looks to work now! I went into the find dialog box and now the box with match entire cell contents was checked.

I would never have figured this out myself, but now that I know it, I surely can see why it wasnt working. I am off to work now, so I will try implementing the new lines of code today and I will report back if its now working for me with my worksheet at work. I surely works on my testsheet here at home!

Thanks again for your time!


Report •

#44
January 9, 2015 at 00:10:14
Okay here is an update:

It is now working. Ive found these problems when playing with this:

1. If I have a blank cell in column A inbetween some ID numbers Excel crashes.
I have tried to Work this out by making a helper column with this in it:

=IF(A2="";"blank line"&" "&ROW();A2)

I have then changed the ID lookup code to look in the helper column BY.
Now I have done the same thing for the number-of-copies, because data validation has the flaw, that if you press delete to it, it fails to do what it is supposted to. So in the number-of-copies helper column I have this code:

=IF(K2="";1;K2)

That prevents that there never will be a 0 number of copies, so that the last line is deleted.

This all Works fine, but it is very very slow. I think its because it has to look through all 1000 lines in the helper column with the text: "Blank line 999" and so on.

Is there some other way to maybe make the code ignore the lines in sheet1 if it either has no ID number or has no number-of-copies specified?

2. Is there some way to make a new column, where you can put in 1 or 0 and if it is 1, then the line doesnt show up in sheet2 or better gets put all the way Down to the buttom, so that you, if you change your mind and that line should be in sheet2, the code has something to look up in sheet2.



Report •

#45
January 9, 2015 at 17:19:57
It seems to me that you are working way too hard to solve your issues.

If you don't want the code to do it's "insert or delete" thing when there is an empty cell in Column A, then tell it to only do it when there is something in the cell:

'Loop through Sheet 1 Column B in reverse order, assume Header row
   For srcRw = lastSrcRw To 2 Step -1
'Only do stuff if cell in Column A contains an ID number
    If Cells(srcRw, "A") <> "" Then     '<-------
'Initialize Row counter

 ******** The Code That Does Stuff *******

    End If
   Next
End Sub

As far as a blank cell in Column F, if the data validation won't prevent users from deleting the number of copies, then use VBA to do it.

Put this code in the Sheet1 module. It will run whenever you make a change to Sheet1 and first check to see if the change was made to column F, then check to see if the Target cell is empty. If it is, it puts a 1 in the cell.

This is a very simplistic check and would need to be enhanced to deal with situations involving multiple cells, etc. but you get the point.

Private Sub Worksheet_Change(ByVal Target As Range)
'If user deletes the value in a cell in Column F, then place a 1 in the cell.
    If Target.Column = 6 Then
     If Target = "" Then Target = 1
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#46
January 11, 2015 at 22:53:32
Well it does still freeze up or get stuck in a loop or something. Somehow it just doesnt like empty cells in column A, if those cells are in between none-blank cells...

It copies the last ID number correctly, but then starts flashing wildly at that ID numbers first row in sheet 2...


Report •

#47
January 12, 2015 at 04:02:19
It sounds like it is possibly looking for/copying empty cells which could take a long time since there are a lot of them following the actual data. Since it doesn't happen with my tests on the short file you have made available, there must be something the different with the actual file you are using. Without the actual file, I don't know what else I can offer.

I can only suggest that you create a file and circumstance where it happens and upload that file for me to test, similar to what you did in #37. Show me the exact problem and I'll see what I can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question