Solved: VBA - del rows where date in string doesn't match filename

Gram123 · Mar 20, 2015

I currently have a PS1 script that takes yesterday's log files from a server, collates them into a single file and emails the file out in csv format. Out of necessity this always includes the first file from today's date, as that file contains the final activity from yesterday as well as the first activity from today.
Problem is, that means some lines of data will appear in today's file and tomorrow's file.

The filename of the daily csv file always contains yesterday's date in the format <7-char filename>dd-mm-yyyy.csv, e.g.:

ABCDEFGH19-03-2015.csv

At the outset, one of these daily csv files will consist of tens of thousands of rows of data, with all data in column A. Each string begins with a date/time stamp, so the values in col A are in the format yyyymmdd_hhmmss<log file information> e.g.:

20150319_093015< >long string of log information

I then run a macro on the csv file, beginning with inserting two new columns to split the data into 2 columns using the formula:

Range("A2").FormulaR1C1 = "=LEFT(RC[2],15)"
Range("B2").FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-15)"

In other words, it places the date & time stamp in one col A, and all the rest of the guff in col B. It then goes off and does a whole bunch of other stuff.

What I need to do first, though (don't mind whether it's before or after the column has been split into two), is delete all rows where the date does not match the filename.
So, in VBA, here's what I need to do:

- Get the filename of the active file as a variable.
- Extract the date from the filename, i.e. chars 8 to 17 - e.g. 19-03-2015
- Convert the variable's format, so it matches the first 8 chars of the values in Column A, i.e. 20150319
- Delete all rows in the data that do not begin with this value.

I realise this could be done by the PS1 script before the daily email is issued, but evidence suggests this would slow down the script considerably, so I'd rather the processing was by the Excel macro on the user's PC, than by the script on the server.

Thanks in advance!

G123

Keebellah · Mar 20, 2015

Hi there,

Try this code:

Code:

Public Sub deleteOtherdayrows(fName As String)
Dim xrow    As Long
Dim lstRow  As Long
Dim fString As String
Dim fdate   As String
lstRow = Cells(Rows.Count, 1).End(xlUp).Row
fString = Mid(fName, 8, 10)
fdate = Right(fString, 4) & Mid(fString, 4, 2) & Left(fString, 2)
Debug.Print fString, fdate
For xrow = lstRow To 1 Step -1
    If Left(Cells(xrow, 1).Value, Len(fdate)) <> fdate Then Cells(xrow, 1).EntireRow.Delete
Next xrow
End Sub

The syntax would be pass the csv filename without the path as parameter

Code:

Sub testing()
Dim tstr As String
tstr = "ABCDEFG20-03-2015.csv"
deleteOtherdayrows fName:=tstr
End Sub

Keebellah · Mar 24, 2015

Hi Gram, busy?

Gram123 · Mar 24, 2015

Yeah man, always!

Thank you very much for the code, Keebs, I'll give it a test today and get back to you!

Gram123 · Mar 25, 2015

Hey Keebs,
I modified the code slightly to grab the Active Workbook name, and to switch screen updating off whilst it runs through the For Next Loop, and it works perfectly!

Dim xrow As Long
Dim lstRow As Long
Dim fString As String
Dim fdate As String
lstRow = Cells(Rows.Count, 1).End(xlUp).Row

fName = ActiveWorkbook.Name
fString = Mid(fName, 8, 10)
fdate = Right(fString, 4) & Mid(fString, 4, 2) & Left(fString, 2)
Debug.Print fString, fdate

Application.ScreenUpdating = False
For xrow = lstRow To 1 Step -1
If Left(Cells(xrow, 1).Value, Len(fdate)) <> fdate Then Cells(xrow, 1).EntireRow.Delete
Next xrow
Application.ScreenUpdating = True
Click to expand...

Great stuff mate, many thanks!

Gram

Keebellah · Mar 25, 2015

Good to hear it works. Yes, I just placed the code and you put the finishing touch
Glad to have been able to help again.

Log in or Sign up

Solved: VBA - del rows where date in string doesn't match filename

Gram123 Thread Starter

Keebellah Trusted Advisor

Keebellah Trusted Advisor

Gram123 Thread Starter

Gram123 Thread Starter

Keebellah Trusted Advisor

Sponsor

Welcome to Tech Support Guy!

Excel: Timesheet - hiding rows based on cell value

Copy rows from several wbooks to another wbook, condition

Log in or Sign up

Solved: VBA - del rows where date in string doesn't match filename

Gram123 Thread Starter

Keebellah Trusted Advisor

Keebellah Trusted Advisor

Gram123 Thread Starter

Gram123 Thread Starter

Keebellah Trusted Advisor

Sponsor

Welcome to Tech Support Guy!

Excel: Timesheet - hiding rows based on cell value

Copy rows from several wbooks to another wbook, condition

Useful Searches