asked on

Excel 2010 - delete/add row based on cell value

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

[ fanpages ]

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

[ fanpages ]

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.

futr_vision

ASKER

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.

futr_vision

ASKER

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.

[ fanpages ]

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

futr_vision

ASKER

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.

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

futr_vision

ASKER

Whoah! That rocks!

[ fanpages ]

:) I thought you'd be impressed!

futr_vision

ASKER

I'm going to close out this question now since the second part of my question is really a separate question.

futr_vision

ASKER

Fantastic help! I needed the actual code given that I am a beginner when it comes to VBA scripting.

[ fanpages ]

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.

futr_vision

ASKER

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

https://www.experts-exchange.com/questions/28144042/Exccel-2010-How-to-run-multiple-web-queries-in-succession-and-record-data-in-single-table.html

[ fanpages ]

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.

futr_vision

ASKER

Unfortunately I can't use the actual URL. I see you already pasted the URL. I didn't have a chance earlier. Thanks.