98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » IF Function
IF Function
Resolved · High Priority · Version 2010
Thu 16 Mar 2017, 11:18Delegate Freddie said...
Freddie has attended:
Excel VBA Introduction course
IF Function
Hi Guys
I have a worksheet of data where I am looking to delete data that has a maturity date of prior month ("MATDATE") from it. I have the below but it does not seem to work:
strFileMonth = Format((DateSerial(Year(Date), Month(Date), 0)), "DDMMYYYY")
Dim strFindWhat As String
strFindWhat = strFileMonth
Sheets("Previous Month Data").Select
Range("D2").Select
If cell Like "*strFindWhat*" Then
ActiveCell.EntireRow.Delete
Do Until ActiveCell.Offset(0, -3) = ""
Loop
End If
Any ideas how to resolve this?
Thanks
Freddie
Fri 17 Mar 2017, 10:45Trainer Jens said...
RE: IF Function
Hi Freddie,
Thank you for the forum question.
I would do something like the code below. To get it right you must go through your records from bottom and up.
I hope this make sense.
Sub RemoveOld()
Dim NumRows As Long
Dim lCounter As Long
NumRows = Range("b2").CurrentRegion.Rows.Count - 1
For lCounter = NumRows To 1 Step -1
If Cells(lCounter, 2).Value < DateSerial(Year(Date), Month(Date), 1) Then
Cells(lCounter, 2).EntireRow.Delete
End If
Next lCounter
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 17 Mar 2017, 13:33Delegate Freddie said...
RE: IF Function
Hi Jens
Thanks for your reply.
It doesn't seem to work. The maturity dates are in column D and the date format in for the maturity date is dd/mm/yyyy.
Would this make a difference?
Thanks
Freddie
Fri 17 Mar 2017, 13:43Trainer Jens said...
RE: IF Function
Hi Freddie,
Sorry I was testing column B.
Try the code below.
Sub RemoveOld()
Dim NumRows As Long
Dim lCounter As Long
NumRows = Range("C2").CurrentRegion.Rows.Count - 1
For lCounter = NumRows To 1 Step -1
If Cells(lCounter, 3).Value < DateSerial(Year(Date), Month(Date), 1) Then
Cells(lCounter, 3).EntireRow.Delete
End If
Next lCounter
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 17 Mar 2017, 14:09Delegate Freddie said...
RE: IF Function
Hi Jens,
Thanks for your reply.
It still however does not seem to work :(
Best,
Freddie
Fri 17 Mar 2017, 14:47Trainer Jens said...
RE: IF Function
Hi Freddie,
I have a feeling that you have blank rows in the CurrentRegion.
Try the code below. If you have more than 10000 records please amend the loop.
Sub RemoveOld()
Dim lCounter As Long
For lCounter = 10000 To 1 Step -1
If Cells(lCounter, 2).Value < DateSerial(Year(Date), Month(Date), 1) Then
Cells(lCounter, 2).EntireRow.Delete
End If
Next lCounter
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 17 Mar 2017, 15:19Delegate Freddie said...
RE: IF Function
Hi Jens
I changed the lCounter to 4 which makes it work now.
Many thanks for your help! have a good weekend!
Best,
Freddie
Fri 17 Mar 2017, 15:22Trainer Jens said...
RE: IF Function
Thanks and a good weekend to you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Generate randon numbersSome types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it: |
