Macro For Moving Variable Number of Rows from a CSV file Monthly

**jaroesner** · 06-24-2013, 12:05 PM

I need a macro that that can open a CSV file named the same monthly and update the range in the macro for the total number of rows that month. I then use that range to run various macros against the CSV file.

**DavidJAllen** · 06-24-2013, 12:32 PM

jaroesner,

Once you have opened the csv file you can select the range of data by using the following

Please Login or Register  to view this content.

If you need to find the number of rows in that range (that is selected) you can use

Selection.Rows.Count

Does that help?

Regards

David

**jaroesner** · 06-24-2013, 01:48 PM

Not really I have a macro now that knows to go to last row but it populates part of my macro with that range i.e. B3:B226 then next month when it runs. It runs fine with the last row statement but has stored the range from previous month B3:B226 for example. I am using Excel 2007. What I need the range for is it needs to copy a formula for counting number of occurences for the errors that occurred the prior month. Which it copied from the CSV file using the select down last row and remove duplicates parameters. I then close the CSV paste the errors and need that range so it can count the number of errors for each occurrence and paste the count into the monthly error sheet. Hope that helps clarify what I am doing.

**DavidJAllen** · 06-24-2013, 02:47 PM

I'm afraid it is not clear to me.

Selection.address will give you the address of the range that is selected if you need to store and use it next month.

**jaroesner** · 06-24-2013, 02:56 PM

Thats just it I do not want it saving the range it does though. I will try and get a snapshot of the macro with the range populated. I need it to re select the range each month. But it opcies the range from the first time it is run and saves it. Someone gave me this lastrow delimiter but it causes the macro to stop at that statement.

**Leith Ross** · 06-24-2013, 03:18 PM

Hello jaroesner,

Rather than posting a snapshot, you should post a copy of the CSV file, along with the path where it is saved, and the other workbook which is to be updated.

If you workbooks contains information that is sensitive, please redact it before you post.

**jaroesner** · 06-24-2013, 04:05 PM

ERRLOG.CSV Errors 2013.xlsm Attachment 245345 Attachment 245346 What will looking at the data tell you? I can do that but I do not know what you are going to see more than how the data looks. Paths are not a problem to get that is listed below:

S:\Operations Specialists Documentation\Processes_Tracker\ERRLOG.CSV [Data File Location]
S:\Operations Specialists Documentation\Processes_Tracker\Summary_Reports\Errors 2013.xls [Worksheet Location]

**DavidJAllen** · 06-25-2013, 04:59 AM

jaroesner,

Check out the attached file. It contains 2 sheets - ERRLOG and May.

There is a macro to show the number of errors by error codes and errors by date. You can put any number of rows in the ERRLOG (I copied your original file in 4 times).

The trick is as follows:

1) when I have your full list of errors selected in ERRLOG I save the range using

ErrorCodeRangeInCSV = Selection.Address(ReferenceStyle:=xlR1C1)

2) when I have your list of errors with duplicates removed I count them

UniqueErrorCodes = Range(Selection, Selection.End(xlDown)).Rows.Count

3) I then build the formula for the countif formula using the range I have saved (ErrorCodeRangeInCSV) and I know the range that needs to have the formulas but the count of UniqueErrorCodes

Range("B3:B" & UniqueErrorCodes + 2).Cells.FormulaR1C1 = "=COUNTIF(ERRLOG!" & ErrorCodeRangeInCSV & ",RC[-1])"

Hope that helps.

Regards

David