Solved

Adding a "blank" Line

Posted on 2013-01-15
31
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
  • 4
31 Comments
 
LVL 47

Expert Comment

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

Author Comment

by:Bright01
ID: 38779963
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 47

Expert Comment

by:Martin Liss
ID: 38779969
If it's not too much trouble.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

by:Martin Liss
ID: 38780416
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
ID: 38780820
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 47

Expert Comment

by:Martin Liss
ID: 38780880
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
ID: 38781085
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 47

Expert Comment

by:Martin Liss
ID: 38781109
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
ID: 38783266
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
ID: 38787118
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
ID: 38809601
Hi Bright01
Do you still need help on this one ?
gowflow
0
 

Author Comment

by:Bright01
ID: 38809858
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
ID: 38810267
no sweat will look at it once I touch base shortly.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38818812
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
 
LVL 47

Expert Comment

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

Author Comment

by:Bright01
ID: 38819171
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 47

Expert Comment

by:Martin Liss
ID: 38819377
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 47

Expert Comment

by:Martin Liss
ID: 38819387
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
ID: 38819501
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
ID: 38819535
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 47

Expert Comment

by:Martin Liss
ID: 38819546
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
ID: 38820429
I'll leave you in good hands I'm sure Martin will get you to shore safely.
gowflow
0
 

Author Comment

by:Bright01
ID: 38820477
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 47

Expert Comment

by:Martin Liss
ID: 38820704
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 47

Expert Comment

by:Martin Liss
ID: 38821058
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
ID: 38821925
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 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38822111
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
ID: 38826362
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
ID: 38826407
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 47

Expert Comment

by:Martin Liss
ID: 38827178
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

763 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