MACRO to Copy/Delete a Range of Rows depending on Location of Cell Value (Dynamic)?

[skomarni]

Hi,

I've avoided posting my own thread for years when it has come to excel help, but this time I am really stuck. I am not too experienced with macros... I'll typically look for an alternative solution or else I record simply copy/delete/hide etc. macros using excel's 'Record Macro'.

I have created a costing program in excel for the industry I'm in which is intended to cost out hundreds of products through the use of individual 12 col/40 row template. Starting with a template range of A1:O40 (Columns L,M,N are always hidden, but must be copied with the rest of the range) I will select the entire range ctrl-c, ctrl-v into cell A41, which duplicates the former range in it's entirety with identical functionality. I use different worksheets for different categories, but products within the same category are copy and pasted directly below one another (A1,A41,A81,A121 etc.). Functionally, the system works fine, however I've been trying to find a way where I can add 3 buttons along the top of each template which will be labeled: DELETE, COPY, NEW.

Here's what I need the macro's to do:
DELETE: I need this macro to be able to delete the row numbers belonging to the current product template being used (ex. If I want to delete the template for the product which is in range A41:080, I could click a button w/ assigned macro within that template to remove the desired rows). Since I'm not sure sure whether a macro can determine the row number of where a button was clicked and delete that row + 39 below it, I was thinking I could add a confirmation cell. In cell G1 (and G41, G81, G121 etc.) I can have the user type "DEL" and then activate the macro. The macro would scan 'Column G' for "DEL" and when it finds it, it would delete that row + the 39 below it? Is this possible?

COPY: Similarly, in J1 (and J41, J81, J121 etc.) I would have the user type "COPY" on the template they wish to copy and activate the copy macro which would scan 'Column J' for "COPY" and when it finds it, it would select that row + the 39 below it and insert that copied range after the last row containing data, and lastly, remove the text "COPY" from the original template and the new.

NEW: This macro needs to copy Rows 1-40 and insert that copied range after the last row containing data, and then delete cell values in these ranges + the row number it was pasted in - 1: (A4, A14:C28, B31:C36, B39:D40, I4:I10, O4:O10, G14, G16, G19, G21, G23:G24, G26, G32:I40 on the original template) so if it was pasted into Row41 (41-1=40) then the values would be (A5, A54-C68, B71:C76, B79:D80, I44:I50, G54, G56, G59, G61, G63:G64, G66, G72:I80).

Unfortunately I can not share the workbook as it contains confidential costing information, but hopefully I have done a well enough job explaining what I am trying to achieve. Let me know if anyone needs anymore details.

I'll gladly take any help I can get on any of these 3 macros I have described.

Thank you so much,
Stephen

 

[asad]

for first two, I suggest you can get user to input the row numbers and macro can then delete or copy those rows.

 

[skomarni]

Hi asad,

As long as the rest of the macro can function as described (for ex. inserting the copied cells after the last row containing data) then that is certainly a valid solution for how to get the macro to distinguish which rows to copy/delete. It's not my favorite solution as there could easily be an input error on the row numbers and I would prefer to remove that human error as much as possible, whereas typing DEL in a particular cell which exists in a particular Row# then running a macro would eliminate or copy the correct 40 rows every time.

But thanks for the good suggestion, anyone who is willing to attempt to create any of the macros in my first post could certainly use your solution for selecting row numbers if they require, although my first choice would be the initial way described.

 

[asad]

I can have the user type "DEL" and then activate the macro. The macro would scan 'Column G' for "DEL" and when it finds it, it would delete that row + the 39 below it? Is this possible?

COPY: Similarly, in J1 (and J41, J81, J121 etc.) I would have the user type "COPY" on the template they wish to copy and activate the copy macro which would scan 'Column J' for "COPY" and when it finds it, it would select that row + the 39 below it and insert that copied range after the last row containing data, and lastly, remove the text "COPY" from the original template and the new.

For this one I will suggest you try something like this
first you protect the column G with only G1, G41, G81 and so on unprotected cells in the column. then you can add something like this

x = cells.find(what:="Del", after:=.Range("G1")).row
y = x+39
.Range("A" & x & ":" & "O" & y).entirerow.delete

I have not tested it, but something like this should work.
Asad

 

[asad]

Ok, Now try this one

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
Dim y As String
Dim r As Range
Set r = Range("G:G")
If Not Application.Intersect(Target, r) Is Nothing Then
Exit Sub
End If
With ActiveSheet
x = .Cells.Find(What:="Del", after:=.Range("G1")).Row
y = x + 39
Range("A" & x & ":" & "O" & y).EntireRow.Delete
End With
End Sub

Let me know if this is what you wanted. If yes, then may be we can make similar one for other two conditions as well. Asad

 

[asad]

With my very limited knowledge, I tried this one as well

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
Dim y As String
x = Application.InputBox("Please enter starting row number")
y = Application.InputBox("Please enter last row number to delete")
    
Range("A" & x & ":" & "O" & y).EntireRow.Delete  
  
End Sub

But this one gives an error if no input detected. Maybe some one can fix this issue on this forum. Asad

 

[asad]

Ok, finally got something that does not give error . First put this code in a module in your workbook

Sub DeleteRows()
Dim x As String
Dim y As String

  With ActiveSheet
  x = .Cells.Find(What:="Del", After:=.Range("G1")).Row
  y = x + 39
  Range("A" & x & ":O" & y).EntireRow.Delete
  End With
End Sub

Then go back to your sheet and insert a button from "Form Control" on Developers Tab and assign this macro to that button. Now when you type Del in G column and hit this button, it will delete that row and 39 more rows from that row. Asad

 

[skomarni]

Wow, thank you so much for your work on these macros asad! Sorry, I spent the last night/day away and had not yet tested these in my workbook. I'll will test them right away!

 

[skomarni]

Ok, finally got something that does not give error . First put this code in a module in your workbook

Sub DeleteRows()
Dim x As String
Dim y As String

  With ActiveSheet
  x = .Cells.Find(What:="Del", After:=.Range("G1")).Row
  y = x + 39
  Range("A" & x & ":O" & y).EntireRow.Delete
  End With
End Sub

Then go back to your sheet and insert a button from "Form Control" on Developers Tab and assign this macro to that button. Now when you type Del in G column and hit this button, it will delete that row and 39 more rows from that row. Asad

Awesome! The code is working as intended: when the confirmation word "DEL" is typed into Col G and the user runs the macro, the product is removed in its entirety (all 40 rows). Very happy that was doable

However, there are two things with the macro that I would consider undesirable:

1. If the macro is run and 'DEL' is not typed anywhere in Col G, I get the error "Run-time error '91': Object variable or With block variable not set" It's easy enough to click 'end', but is there anyway to either not have that specific error pop-up (I could see it being confusing to the majority of users who do not understand the error or why it is showing up) or to at least have a different message such as "Confirmation "DEL" for delete not found" to advise the user?

2. Every time a product is removed, a small portion of the form button remains at the bottom of the product above it... but the weird thing is that this happens no matter where I put the button on the product template. Basically, all 40 rows get removed, but the form button is squished to something that looks like a thick border and lays in between every 40th and 41st row depending on where the row deletion took place. You could right click the little sliver of form button and delete it, but it's just weird why it would remain in the first place.

If you or anyone else could do what you did for the Delete macro, and create one where it would copy those 40 rows and insert them after the last row containing data, that would be swell!

 

[asad]

1. If the macro is run and 'DEL' is not typed anywhere in Col G, I get the error "Run-time error '91': Object variable or With block variable not set" It's easy enough to click 'end', but is there anyway to either not have that specific error pop-up (I could see it being confusing to the majority of users who do not understand the error or why it is showing up) or to at least have a different message such as "Confirmation "DEL" for delete not found" to advise the user?

2. Every time a product is removed, a small portion of the form button remains at the bottom of the product above it... but the weird thing is that this happens no matter where I put the button on the product template. Basically, all 40 rows get removed, but the form button is squished to something that looks like a thick border and lays in between every 40th and 41st row depending on where the row deletion took place. You could right click the little sliver of form button and delete it, but it's just weird why it would remain in the first place.

If you or anyone else could do what you did for the Delete macro, and create one where it would copy those 40 rows and insert them after the last row containing data, that would be swell!

One possible way is to not have the button in sheet. Instead of button assign a short key and that will then reduce the possibility of someone hitting the macro button by mistake and also at the same time you will not have the mini version of button between the rows. As for the other macro for copying, that can be done on same basis as the current macro. I will have a try and let you know how I go. Asad