Excel- VBA help

 

I am working on a spreadsheet that has data I need to remove based on three different cells. Here is my goal:

If A1=31 AND B2 contains a date > than the date in C2, delete the entire row. I need to loop through the full data set and check each row. I have it removing the row if A1=31 and looping through the worksheet, but can’t seem to add the date component.

Here is my coding. It is probably kind of messy since mostly copied and pasted. I am not an a very experienced coder and don’t really care what it looks like as long as it works. I am also not working with massive datasets so speed isn’t an issue.

Sub Remove_Future_Dates() Dim Firstrow As Long Dim LastRow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long

Firstrow = ThisWorkbook.Worksheets(2).UsedRange.Cells(1).Row LastRow = ThisWorkbook.Worksheets(2).Cells(ThisWorkbook.Worksheets(2).Rows.Count, “A”).End(xlUp).Row

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    '.ScreenUpdating = False
End With
With ActiveSheet
    .Select
    Firstrow = .UsedRange.Cells(1).Row
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Lrow = LastRow To Firstrow Step -1

        With .Cells(Lrow, "A")

            If Not IsError(.Value) Then

                If .Value = "31" Then .EntireRow.Delete
            End If
            End With

    Next Lrow

End With

End Sub

 

If A2= 31

B2 = 5/1/19

C2 = 4/1/19

The macro would remove the entire row since A2=31 and the date in B2 is greater than 4/1/19.

By: Travis

15 thoughts on “Excel- VBA help

  1. happy.steelhome.cn

    Aw, this was an extremely good post. Finding the time and actual effort to create a superb article… but what can I say… I procrastinate a lot and never manage to get nearly anything done.

  2. vurtil opmer

    I think other web-site proprietors should take this website as an model, very clean and wonderful user friendly style and design, let alone the content. You’re an expert in this topic!

  3. sling tv

    I was recommended this blog via my cousin. I am now not certain whether or not this put up
    is written through him as no one else know such designated about
    my trouble. You’re wonderful! Thanks!

  4. sling tv

    Do you have a spam problem on this site; I also am a blogger, and I was wondering your
    situation; we have developed some nice methods and we are looking to swap techniques with others, be sure to shoot me an e-mail if interested.

  5. coconut oil

    Hey just wanted to give you a quick heads up.
    The text in your post seem to be running off
    the screen in Chrome. I’m not sure if this is a formatting issue or something to do with browser compatibility but I figured I’d post to let you know.

    The layout look great though! Hope you get the issue resolved soon. Many thanks

  6. ps4 games

    My brother recommended I may like this web site. He was once totally right.
    This put up actually made my day. You can not consider just how a lot time I had spent for this information! Thank you!

  7. ps4 games

    Very nice post. I just stumbled upon your weblog and wanted to say that I’ve truly enjoyed
    browsing your blog posts. In any case I’ll be subscribing to your
    rss feed and I hope you write again soon!

  8. plenty of fish dating site

    I’m amazed, I must say. Rarely do I encounter a blog that’s both equally educative and interesting,
    and without a doubt, you’ve hit the nail on the head. The
    issue is something which not enough men and women are speaking intelligently about.
    I’m very happy that I came across this in my
    hunt for something concerning this.

  9. then coconut oil

    Do you mind if I quote a few of your posts as long as
    I provide credit and sources back to your site? My blog site is in the exact same area of interest as yours and my visitors would definitely benefit
    from some of the information you present here. Please let me know if this ok
    with you. Regards!

  10. tinyurl.com

    Hmm is anyone else encountering problems with the pictures on this blog loading?
    I’m trying to figure out if its a problem on my end or if it’s the blog.

    Any feed-back would be greatly appreciated.

Comments are closed.