Solved

Excel 2010 - delete/add row based on cell value

Posted on 2013-05-29
15
808 Views
Last Modified: 2013-05-31
I've attached a sample doc to illustrate what I am looking to do. First, I want to delete any row where the both the Start Date and End Date are earlier that 2010 or add rows until I get to a point where the Start Date and End Date are both earlier than 2010. The values in those cells are controlled by "Initial Date" and "Interval" cells at the top.

Second, and I am not sure how to build this out in an example doc, is to place resulting data in a single continuous table. The URLs you see in the spreadsheet will be submitted to a webpage as query which will return several rows of data. That data I would like to put in a new worksheet but in a continuous table. Since the first two rows of the returned data contain heads I would need to remove those before pasting to the next available row in the table.

Hope this makes sense.
Date-sample.xlsx
0
Comment
Question by:futr_vision
  • 8
  • 7
15 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39206390
Hi,

Regarding the first part (paragraph) of your question, please find attached a workbook that contains the following code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142299.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28142299
' Question Title:   Excel 2010 - delete/add row based on cell value
' Question Asker:   futr_vision                               [ http://www.experts-exchange.com/M_4958738.html ]
' Question Dated:   2013-05-29 at 21:33:25
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnWend                                           As Boolean
  Dim lngRow                                            As Long
  
  On Error GoTo Err_Worksheet_Change
  
  If Target.Address = Range("INITIAL_DATE").Address Or _
     Target.Address = Range("INTERVAL").Address Then
     
     If IsDate(Range("INITIAL_DATE")) And _
        IsNumeric(Range("INTERVAL")) Then
        
        Application.StatusBar = "Please wait..."
        Application.ScreenUpdating = False
        Application.EnableEvents = False
     
        Range([A5], [C5].End(xlDown)).ClearContents
     
        lngRow = 4&
        blnWend = False
     
        While Not (blnWend)
      
            DoEvents
         
            lngRow = lngRow + 1&
         
            Select Case (lngRow)
             
                Case (5&)
                    [A5].Formula = "=(B5-$B$2)+1"
                    [B5].Formula = "=A2"
                    [C5].Formula = "=""http://www.ABC.com?start_from="" & Text(A5, ""yyyy-mm-dd"") & ""&date_to="" & Text(B5, ""yyyy-mm-dd"")"
             
                Case (6&)
                    [A6].Formula = "=(B6-$B$2)+1"
                    [B6].Formula = "=A5-1"
                    [C6].Formula = "=""http://www.ABC.com?start_from="" & Text(A6, ""yyyy-mm-dd"") & ""&date_to="" & Text(B6, ""yyyy-mm-dd"")"
             
                Case Else
                    Rows(6&).Copy Destination:=Rows(lngRow)
         
            End Select
         
            blnWend = ((Cells(lngRow, "A") < DateSerial(2010, 1, 1)) And (Cells(lngRow, "B") < DateSerial(2010, 1, 1)) Or _
                       (lngRow = Cells.Rows.Count))
         
        Wend
        Beep
     End If ' If IsDate(Range("INITIAL_DATE")) And IsNumeric(Range("INTERVAL")) Then
  End If ' If Target.Address = Range("INITIAL_DATE").Address Or Target.Address = Range("INTERVAL").Address Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change

End Sub

Open in new window


Please change the "Initial Date" &/or the "Interval" values & wait for the worksheet rows to be updated.

Please note that the routine could be re-written to run differently/quicker by calculating how many rows needed to be created in advance, rather than checking the "Start Date" & "End Date" values on an individual row-by-row basis.

I have not written the code to do this (at present) in case you need to change the formulae you had provided in your sample workbook (above) during the development of the rest of the solution to address the second part (paragraph) of your question.

I will post my concerns on your requirements for the remaining element of your question in a follow-up comment below.

BFN,

fp.
Q-28142299.xlsm
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39206399
Second, and I am not sure how to build this out in an example doc, is to place resulting data in a single continuous table. The URLs you see in the spreadsheet will be submitted to a webpage as query which will return several rows of data. That data I would like to put in a new worksheet but in a continuous table. Since the first two rows of the returned data contain heads I would need to remove those before pasting to the next available row in the table.

Are you asking for the individual URLs to be queried & for the results of each request to the named web site to be captured within a new worksheet (ignoring any header information that will be returned from the query request)?

In order to achieve this, please can you advise on the true domain name/page request you will be using, rather than the, what I presume is, dummy URLs for the ABC.com (ABC Network) domain?

Thank you.
0
 

Author Comment

by:futr_vision
ID: 39208400
Ok. For the first part if I simply added another value at the top for the overall end date that would make programming easier? That might be an option. Maybe if I use a calendar widget there instead of text that could definitely work.

For the second part I had found this video which will work for me(after I log into the site)
http://www.youtube.com/watch?v=qbOdUaf4yfI

Problem with this solution is that it puts the resulting data on its own worksheet. i would like to add each resulting dataset to an existing worksheet but at the bottom on the previous results. As i mentioned the data is going to come with column titles that take up two rows. Those would need to be deleted somehow. I could do it manually but the more automation I can put in place the better.
0
 

Author Comment

by:futr_vision
ID: 39208405
Ah. one other thing I noticed. In you sample workbook it runs as intended but the last row contains data that is completely in 2009. That would need to be deleted too.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39208769
Hi,

You initially requested:
...add rows until I get to a point where the Start Date and End Date are both earlier than 2010

Your latest comment contradicts that request:
Ah. one other thing I noticed. In you sample workbook it runs as intended but the last row contains data that is completely in 2009. That would need to be deleted too.

Please find attached a workbook that addresses your revised requirements.

BFN,

fp.
Q-28142299b.xlsm
0
 

Author Comment

by:futr_vision
ID: 39209282
Ah. I see how that can be confusing. Sorry about that. This works great although I am curious how much faster it would be the other way you mentioned.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39209531
Hi,

I think you will find this revision runs a little faster than the previous two versions:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142299.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28142299
' Question Title:   Excel 2010 - delete/add row based on cell value
' Question Asker:   futr_vision                               [ http://www.experts-exchange.com/M_4958738.html ]
' Question Dated:   2013-05-29 at 21:33:25
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim lngLast_Row                                       As Long
  
  On Error GoTo Err_Worksheet_Change
  
  If Target.Address = Range("INITIAL_DATE").Address Or _
     Target.Address = Range("INTERVAL").Address Then
     
     If IsDate(Range("INITIAL_DATE")) And _
        IsNumeric(Range("INTERVAL")) Then
        
        Application.StatusBar = "Please wait..."
        Application.ScreenUpdating = False
        Application.EnableEvents = False
     
        Range([A5], [C5].End(xlDown)).ClearContents
     
        lngLast_Row = 4& + Application.WorksheetFunction.RoundUp(([A2] - DateValue("31/12/2009")) / [B2], 0)
        
        [A5].Formula = "=(B5-$B$2)+1"
        [B5].Formula = "=A2"
        Range([C5], Cells(lngLast_Row, 3)).Formula = "=""http://www.ABC.com?start_from="" & Text(A5, ""yyyy-mm-dd"") & ""&date_to="" & Text(B5, ""yyyy-mm-dd"")"
        
        Range([A6], Cells(lngLast_Row, 1)).Formula = "=(B6-$B$2)+1"
        Range([B6], Cells(lngLast_Row, 2)).Formula = "=A5-1"
        
        Beep
     End If ' If IsDate(Range("INITIAL_DATE")) And IsNumeric(Range("INTERVAL")) Then
  End If ' If Target.Address = Range("INITIAL_DATE").Address Or Target.Address = Range("INTERVAL").Address Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change

End Sub

Open in new window


BFN,

fp.
Q-28142299c.xlsm
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:futr_vision
ID: 39209548
Whoah! That rocks!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39209642
:) I thought you'd be impressed!
0
 

Author Comment

by:futr_vision
ID: 39210591
I'm going to close out this question now since the second part of my question is really a separate question.
0
 

Author Closing Comment

by:futr_vision
ID: 39210593
Fantastic help! I needed the actual code given that I am a beginner when it comes to VBA scripting.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39210611
You're very welcome.

If/when you create a separate question, please refer to this one (with a link, if possible) so that the (other) "Experts" have a point of reference.

Good luck with the rest of your project.
0
 

Author Comment

by:futr_vision
ID: 39211480
I'll update my question to include a link although I am not sure it is necessary. Here is the new question.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28144042.html
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39211894
I certainly appreciate the background to a project especially when working on proposals to address (further) requirements that may affect/influence the outcome of solutions provided by other "Experts".

There may be a conflict in a proposal that is not evident from the amount of information in a single question thread, & this may unintentionally stop a previous solution from continuing to function (correctly).

I would hope that others take the same approach... but perhaps not.

Thanks for providing the link back here (& vice versa) in any respect.

PS. I note that you are still referring to "ABC.com" rather than the actual site you will be using.

Are you not able to quote the actual URL?  Being able to reproduce your needs exactly would help those offering solutions by allowing all potential pitfalls to be encountered prior to producing a finished product for you.
0
 

Author Comment

by:futr_vision
ID: 39211943
Unfortunately I can't use the actual URL. I see you already pasted the URL. I didn't have a chance earlier. Thanks.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

11 Experts available now in Live!

Get 1:1 Help Now