Results 1 to 2 of 2

Run Delete Table Rows macro with condition on Date change

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    188

    Run Delete Table Rows macro with condition on Date change

    When the Date value in cell B2 is changed, I would like the Delete_Table_Rows macro (see below) to run prior to the rest of the code contained in the Worksheet_Change code (also below). This macro should only run if there is data in the 38th row and beyond (39, 40, 41, etc.). This is necessary in order to avoid having the macro throw an error. Please note that I am referring to table row and not worksheet row. I tried adding a Call Delete_Table_Rows line somewhere in the Change code but haven’t been successful. It seems perhaps something else must be written with it to allow this to be compatible. Please help!

    My goal is to have this table be able to reset itself and accept new data. I somehow will need to find a way to clear out the C2:C38 cells as well but, at the moment, am satisfied with doing so manually.

    Public Sub Delete_Rows_and_Clear_Cells_In_Table()
        
        Dim table As ListObject
        Set table = Sheets("Phase").ListObjects("Phaseline")
        With table
            'Delete 38th data row to last data row
            .DataBodyRange.Rows("38:" & .ListRows.Count).Delete
            
        End With
        
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim intersection As Range
        Set intersection = Intersect(Target, Range("B2"))
        If Not intersection Is Nothing Then
        
        Dim lLastrow As Long, oldDate As Date, newDate As Date
        With Sheets("Phase")
            lLastrow = .Range("B" & .Rows.Count).End(xlUp).Row
            oldDate = .Range("B37").Value
            newDate = Date
            Dim lEndrow As Long
            lEndrow = lLastrow + 367
            Do While oldDate < newDate And lLastrow <= lEndrow
                oldDate = oldDate + 1
                lLastrow = lLastrow + 1
                .Range("B" & lLastrow).Value = oldDate
            Loop
        End With
        End If
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to add a second condition to a macro that will delete rows
    By jasgilm77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2015, 02:23 AM
  2. [SOLVED] Macro to delete rows ONLY if condition is met
    By lealea1982 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 06:03 AM
  3. Macro to delete rows if certain condition is met
    By mjali001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2011, 05:20 AM
  4. delete rows with date condition
    By hagop_b in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-02-2009, 05:12 PM
  5. macro to delete rows with a condition
    By Foxcan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-29-2009, 01:49 PM
  6. Macro to Delete Specific Rows (Condition)
    By leviathan86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2009, 06:16 AM
  7. VB delete Access rows from table on condition
    By ceemo in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-02-2006, 03:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1