Results 1 to 10 of 10

Find date and delete all below it

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Find date and delete all below it

    I have the below which doesn't work for some reason. I want it to find the date in P1 in column B and when it finds the date that will be in P1 to delete that row it finds it on and all rows below it.

    Sub test()
    
    
    'adds 7 days to date in B2 in Fixtures and deletes all rows below this date
        Range("P1").Select
        ActiveCell.FormulaR1C1 = "=R[1]C[-14]+7"
        Range("P1").Select
        Selection.NumberFormat = "dd/mm/yyyy"
    
    'find the new date above in column B and delete all rows below it
    Dim LR As Long, Found As Range
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Set Found = Columns("B").Find(What:=P1, LookIn:=xlValues, LookAt:=xlWhole)
    If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
    
    
    End Sub
    Last edited by ScabbyDog; 03-25-2013 at 12:41 PM.

Thread Information

Users Browsing this Thread

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

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