Consulting

Results 1 to 3 of 3

Thread: Delete columns based off of formula in another cell

  1. #1
    VBAX Newbie
    Joined
    Nov 2015
    Posts
    2
    Location

    Question Delete columns based off of formula in another cell

    This is my first time posting, I'm desperate for help and can't find how to do this.

    I have a macro that inserts a formula into cell H2, =Today()-90

    I have another column "E" that has dates, some blank though. I'd like to select all of E and delete any row with a value that is greater than the result of the formula in H2.

    I'm a novice and this has me stumped, I appreciate any help.

    Thanks

  2. #2
    VBAX Newbie
    Joined
    Nov 2015
    Posts
    2
    Location
    I am looking at this wrong. I have a code that works but is not handling blanks well.

    Dim LR As Long, I As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    LR = Range("E" & rows.Count).End(xlUp).Row
    
    For I = LR To 1 Step -1
        If Range("E" & I).value <= Date - 90 Then rows(I).Delete
    Next I
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    It seems to stop when a cell in column E is blank. I cannot figure how to ignore the blanks.
    Last edited by SamT; 11-30-2015 at 03:11 PM. Reason: Added CrLf's to code

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Add a condition to the If statement.

    For I = LR To 1 Step -1
    With Range("E" & I)
        If .value <> "" And Value <= Date - 90 Then rows(I).Delete
    End With
    Next I
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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