Adding a "blank" Line

EE Pros,

I have a nice macro that automatically adds a new line when "fired" based on a field name reference (Priority_Value_Case).

Here's the problem.  If I have added data to the cells PRIOR to firing the macro, the macro automatically copies the range that has the added data.  What I want is a new row that does not have the data I've put into the prior record / row.  I think I can do this with a  "clearcontents" command but don't know how for it to look at that added line only.

Here is the actual code:

'This code adds a new Case to the end of the last case.
'This adds a new case up to 12 cases

Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range

Application.DisplayAlerts = False

    Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
If rngNewCase.Row > 19 Then             'To limit to A19               'comment out one of the two
'if rngnewcase.row - 6 > 10 then        'To limit to 10 rows       'comment out one of the two
    MsgBox "you have exceeded the number of entries permitted"
    Exit Sub
End If

   Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase

   
   ChartNo = rngNewCase.Row / 3
   
    Set rngCurrent = rngNewCase.Offset(, 2)
    Set rngDesired = rngNewCase.Offset(, 3)
     
End Sub

Thank you in advance,

B.
Bright01Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngRow As Long

    'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
     For lngRow = 30 To 7 Step -1
        If Range("A" & lngRow).Interior.Color <> 16777215 Then
            Set rngNewCase = ActiveSheet.Range("A" & lngRow + 1) '.End(xlUp).Offset(1)
            Exit For
        End If
    Next
    
    If rngNewCase.Row > 19 Then             'To limit to A19               'comment out one of the two
    'if rngnewcase.row - 6 > 10 then        'To limit to 10 rows       'comment out one of the two
        MsgBox "you have exceeded the number of entries permitted"
        Exit Sub
    End If

    Worksheets("Risk_Return").Range("Priority_Case").Copy rngNewCase
    
    ChartNo = rngNewCase.Row / 3
    
    Set rngCurrent = rngNewCase.Offset(, 2)
    Set rngDesired = rngNewCase.Offset(, 3)
    
    Range("A" & rngDesired.Row & ":" & "D" & rngDesired.Row).ClearContents
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Can you show by example the before and after and where you want the blank row?
0
 
Bright01Author Commented:
MartinLiss,

Thanks for asking!  Let me take one try at explaining this; if you still need the example, I'll mock one up.

The Range name covers A10:H10 and simply copies it when the Macro is fired.  If I have added data in the cells (A10:H10) then it copies all of it to the next row down.  What I'm looking for is the one line of code that clears the data (not formatting)  in what becomes A11:H11.  Then I add data to the new line and when I fire it again, I want it to again, replicate the line and clear any of the data.

Here is the line where the copy is created:


Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase

   
   ChartNo = rngNewCase.Row / 3
   
    Set rngCurrent = rngNewCase.Offset(, 2)
    Set rngDesired = rngNewCase.Offset(, 3)

Do I need to mock up a WS?

B.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Martin LissOlder than dirtCommented:
If it's not too much trouble.
0
 
Bright01Author Commented:
OK....here it is.  You will see the line I'm trying to replicate.
Example-of-adding-a-Case.xlsm
0
 
Martin LissOlder than dirtCommented:
Sorry but I'm still not exactly sure what you need. In any case I added a couple of buttons. My apologies if you already know how to do the things they do.
Example-of-adding-a-Case.xlsm
0
 
Bright01Author Commented:
MartinLiss,

Thank you!  What I'm looking for is when you fire "Add Case" it simply replicates the line above until it hits a count of 15, then delivers the message in the code "You've exceeded the capacity of the model......".

Is that doable?

B.
0
 
Martin LissOlder than dirtCommented:
Try this. I commented out some lines that you may need so be careful.


Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long

'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
    lngLastRow = Range("B65536").End(xlUp).Row

'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(2)
'    If rngNewCase.Row > 24 Then
    If lngLastRow > 23 Then
        MsgBox "you have exceeded the number of entries permitted"
        Exit Sub
    End If
    Rows(lngLastRow & ":" & lngLastRow).Select
    Selection.Copy
    Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
    ActiveSheet.Paste

   'Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase

   
'   ChartNo = rngNewCase.Row / 3
   
'    Set rngCurrent = rngNewCase.Offset(, 2)
'    Set rngDesired = rngNewCase.Offset(, 3)
'    ActiveSheet.Protect Password:="jam"
'    ActiveSheet.EnableSelection = xlUnlockedCells

Open in new window

   
End Sub
0
 
Bright01Author Commented:
MartinLiss,

This works "fairly well".  Only one issue.  When I fire the macro, it does copy the appropriate line to the next line, but I get a hashlined (like a copy command) around the line it is copying. Is there a way of keeping that from showing up?

B.
0
 
Martin LissOlder than dirtCommented:
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long

'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
    lngLastRow = Range("B65536").End(xlUp).Row

'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(2)
'    If rngNewCase.Row > 24 Then
    If lngLastRow > 23 Then
        MsgBox "you have exceeded the number of entries permitted"
        Exit Sub
    End If
    Application.ScreenUpdating = False

    Rows(lngLastRow & ":" & lngLastRow).Select
    Selection.Copy
    Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

   'Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase

   
'   ChartNo = rngNewCase.Row / 3
   
'    Set rngCurrent = rngNewCase.Offset(, 2)
'    Set rngDesired = rngNewCase.Offset(, 3)
'    ActiveSheet.Protect Password:="jam"
'    ActiveSheet.EnableSelection = xlUnlockedCells
     
End Sub

Open in new window

0
 
Bright01Author Commented:
MartinLiss,

Almost perfect!  Here's the final problem; If I add a line, without any input, it works perfectly.  If I fill out the responses in the last line, THEN add a line, it simply copies the line above, with the additions into the new line.  I need the single line of macro code, that "clears" the input BEFORE it copies to the next line.  Make sense?

B.
0
 
Bright01Author Commented:
MartinLiss,

I think what I need is a line like;

.range (?????).ClearContents

that is between when the row is copied and when it actually pastes it or just after it pastes it.  I just don't know how to describe the particular copied line in the range........

B.
0
 
gowflowCommented:
Hi Bright01
Do you still need help on this one ?
gowflow
0
 
Bright01Author Commented:
Gowflow, thanks for touching base....not sure what happened to MartinLiss; hope he is all right.  To answer your question, "yes" I do need this one little thing completed.  Here's the issue.  The macro I have that Martin worked on for me needs to be able to produce a identical line (copied), but without any of the data that is in the line.  In other words, the code properly works; it's just that when you have put data in the first line and then go to add/replicate the line, it copies the line AND the data instead of just the line.  I think all it needs is one line of code that "clears content" (perserving the drop downs, conditional formatting, etc. etc. -- just no data), in the newly created line.

Make sense?  Do I need to send you the latest code or can you see it from Martin's 1/15 post above?

Thank you,

B.
0
 
gowflowCommented:
no sweat will look at it once I touch base shortly.
gowflow
0
 
gowflowCommented:
Sorry for the dealy I was caught in other rushed issues.

I looked up your file and fixed the clear content but then noticed that for Col A you have a drop down that its values are picked from a list in Col T and every time we insert a line it messes the validation as they are in the way. So I moved the validation list onto a separate Validation sheet (that we can hide if you want but now left it for you to look at and make sure it is a complete list).

So try the Add Case and see if this is what your looking for.
gowflow
Example-of-adding-a-Case.xlsm
0
 
Martin LissOlder than dirtCommented:
not sure what happened to MartinLiss; hope he is all right
I'm still here. How can I help?
0
 
Bright01Author Commented:
MartinLiss and Gowflow,

I want to thank both of you for the help on this one.  MartinLiss, when I didn't hear from you I added two questions to try to reengage.   Gowflow reached out and asked if I still needed help.  I'm glad to hear you are all right.

So, the file that Gowflow sent me is built on what you did MartinLiss but I need to be able to simply add a line with no data in it.  Gowflow's fix does that but only adds one line.  I need to add a new line each time I fire the macro.

Again, appreciate all of the help........ I'm certain either of you can finish this out but one of you may want to take it and declare it since both of you probably don't want to work on the same code.

B.
0
 
Martin LissOlder than dirtCommented:
Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range
Dim lngLastRow As Long

'ActiveSheet.Unprotect Password:="jam"
Application.DisplayAlerts = False
    lngLastRow = Range("B65536").End(xlUp).Row
'Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(2)
'    If rngNewCase.Row > 24 Then
    If lngLastRow > 23 Then
        MsgBox "you have exceeded the number of entries permitted"
        Exit Sub
    End If
    Application.ScreenUpdating = False

    Rows(lngLastRow & ":" & lngLastRow).Select
    Selection.Copy
    Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Select
    ActiveSheet.Paste
    Rows(lngLastRow + 1 & ":" & lngLastRow + 1).ClearContents
   'new
    Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"

   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

   'Worksheets("Outcome_Value").Range("Priority_Value_Case").Copy rngNewCase

   
'   ChartNo = rngNewCase.Row / 3
   
'    Set rngCurrent = rngNewCase.Offset(, 2)
'    Set rngDesired = rngNewCase.Offset(, 3)
'    ActiveSheet.Protect Password:="jam"
'    ActiveSheet.EnableSelection = xlUnlockedCells
     
End Sub
0
 
Martin LissOlder than dirtCommented:
Sorry, that new line should be

   Cells(lngLastRow + 1, 1).Value = "Strategy"

and not


Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
0
 
Bright01Author Commented:
MartinLiss,

Thank you.  I have added your code and can't get it to work.  So what I've done is attached a new version with both macros.  You will see + Add Old and + Add New buttons.  The Old is the old code that almost works.  The New is the new code you just sent me.  In order to understand the problem,

1.) Save the file (you'll need to restore it after trying the + New macro)
2.) Press the + Old macro and you will see lines replicated below each line.
3.) Hit the erase button.
4.) Now put some text in cell B7 .... any thing.
5.) Now again, press the + Old macro and you will see that it copies B7 and the line with the text.  That text should not be there....it should be a blank new line.

The new code has problems with some data that rotates the Activity numbers.  It can be moved to anther set of cells and can be redirected in the associated macro.... you don't need to worry about that.

Hope that helps.

B.
Risk-Graphic-v5.xlsm
0
 
Bright01Author Commented:
OK....I changed the code with the new line and moved the data below.  HOwever, still get an error in the +New Line.  It should only replicate A7:D7 and not have any data content in the new line.
Risk-Graphic-v6.xlsm
0
 
Martin LissOlder than dirtCommented:
I copied goflow's workbook that he attached in post ID: 38818812. I then modified the code that I posted above. I'm attaching the result. What the code does is that it it adds 'blank' lines with "Strategy" in column A. The text is there so that the code can determine the last line. If putting that text there is a problem I could put some sort of indicator in some cell off-screen like "AA1" and use that to determine the last row.
Example-of-adding-a-Case-1.xlsm
0
 
gowflowCommented:
I'll leave you in good hands I'm sure Martin will get you to shore safely.
gowflow
0
 
Bright01Author Commented:
MartinLiss,

It isn't the right code and doesn't work.  Look at the version I sent you.  There is no word "Strategy".  What I'm trying to do is simply create another record A:D as what happens when you click on + Line Item - Old.  The problem is simple.  When you only have 1 line, and you type in something in A:D, and then you click on +Line Item -Old, it copies the cells (A:D) and all the data in the first row.  I'm trying to get it to copy the cells (with formatting, dropdown, etc.) WITHOUT the data copied.....so a fresh cell is presented.

Here is the latest copy.
Risk-Graphic-v7.xlsm
0
 
Martin LissOlder than dirtCommented:
Sorry for the confusion. I'm away from my desk but should be able to get back to you in a few hours.
0
 
Martin LissOlder than dirtCommented:
I don't see any dropdowns in columns A to D. anyhow try this

Sub NewFinCase()
Dim rngNewCase As Range
Dim cht As ChartObject
Dim rngCurrent As Range
Dim rngDesired As Range

    Set rngNewCase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
If rngNewCase.Row > 19 Then             'To limit to A19               'comment out one of the two
'if rngnewcase.row - 6 > 10 then        'To limit to 10 rows       'comment out one of the two
    MsgBox "you have exceeded the number of entries permitted"
    Exit Sub
End If

   Worksheets("Risk_Return").Range("Priority_Case").Copy rngNewCase
   
   ChartNo = rngNewCase.Row / 3
   
    Set rngCurrent = rngNewCase.Offset(, 2)
    Set rngDesired = rngNewCase.Offset(, 3)
    
   Range("A" & rngDesired.Row & ":" & "D" & rngDesired.Row).ClearContents

Open in new window

0
 
Bright01Author Commented:
MartinLiss,

This is almost right!  And you are correct...my mistake, there are no dropdowns -- but if there were, I would not want them "erased" in the copy/paste (i.e. still available on the new line).

The only flaw in this is that whenever the macro is fired, it needs to add a new line.  Right now it only adds one more new line.  

I did add text, and you have solved the problem of not copying the text to the new line.  Now if we can add a line each time the macro is fired, that would be perfect!

Thank you for hanging in with me on this one.

B.
0
 
Bright01Author Commented:
Perfect!  I thought this was going to be easy; thank you very much for sticking with me on this.... I can now proceed with my model.  Again, really appreciate the code and  the lesson.

Just curious, how could I have made this easier for you?  One of the great things about EE is that you have to learn how to communicate and interpret the requirements via email/text.  It makes both the source and the provider better communitors.  I would like to get better at explaining what I need.

Thanks again!

B.
0
 
Bright01Author Commented:
MartinLiss,

Your code works great!  I'm going to open an additional question with EE to fix the "Prioritize" macro.  The problem is simple; and I missed it until I tested it.  Here's the issue; when a new line is added, the first cell needs to have a "1" in it.  If it doesn't, I get an error code (400) when I fire the "prioritize" macro.   I hope you will pick this one up; no one knows this code better.

Much thanks,

B.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help. BTW I would have given you the answer to your new question for free:) And as far as explaining what you need, one thing you could have done do to make it easier for us was to say something like the following. Note that the addresses are guessed at for this post.
Here's what I need to have happen. In the attached workbook when I click the <caption of the button> command button, I want a row that is completely blank to appear in Cells A11 to D11, and if clicked again in cells A12 to D12. I also want to limit the number of times the button can be clicked so that row 25 is the last possible line.

In other words be very specific about the results you want to see.

Marty - MVP 2009 to 2012
0
All Courses

From novice to tech pro — start learning today.