Solved

Adding a "blank" Line

Posted on 2013-01-15
31
147 Views
Last Modified: 2013-01-28
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.
0
Comment
Question by:Bright01
  • 14
  • 13
  • 4
31 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Can you show by example the before and after and where you want the blank row?
0
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
If it's not too much trouble.
0
 

Author Comment

by:Bright01
Comment Utility
OK....here it is.  You will see the line I'm trying to replicate.
Example-of-adding-a-Case.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi Bright01
Do you still need help on this one ?
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no sweat will look at it once I touch base shortly.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
not sure what happened to MartinLiss; hope he is all right
I'm still here. How can I help?
0
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry, that new line should be

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

and not


Rows(lngLastRow + 1 & ":" & lngLastRow + 1).Value = "Strategy"
0
 

Author Comment

by:Bright01
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I'll leave you in good hands I'm sure Martin will get you to shore safely.
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry for the confusion. I'm away from my desk but should be able to get back to you in a few hours.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Bright01
Comment Utility
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
 

Author Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now