removing rows & dates from a vlookup table

This workbook has a table which is filled using Vlookup. The Vlookup formula is =IFERROR(VLOOKUP($A8,Sheet5!$A$6:$E$10000,2,FALSE),"")

I want to remove empty rows from the table in Sheet1, but the table needs to be kept at 50 rows. (in other words I want 50 rows of data with no blank rows)

The table in Sheet5 is filled with data written by a macro.

Is there a solution here which does not involve tweaking the macro?

Thanks!

Sheet5 is not exist the attached file?!

Just a curiosity question. How is column A populated? It probably won't affect the solution. Formulas in column A will be clobbered and replaced with the fixed value for the date. The formulas on the other columns are not touched.

The code presented here works "after the fact." Apply it after the data is added to Sheet5 and the lookups have occurred. The Macro name is RemoveRow.

Hi, thank you. If you're referring to column A in Sheet 1, it will always start with today()-50, then add 1 day {meaning, 1 day which has data} to create each subsequent row.

Could your macro be incorporated to other macros in the workbook (there are several) so that each time sheet 5 changes, so will the data in sheet 1?

And if I'm running formulas on the data in sheet 1 will those formulas continue to work after the macro has done its job? (the objective is to use those 50 columns as the dataset from which to do a number of calculations)

It would be simple enough to add a macro to put the proper dates on sheet1. Would you mind if I converted Sheet 1 to an Excel table? That would solve the issue of "copying down the formulas."

Here you go. A revised RemoveRow and a MakeSheet1 that populates sheet 1. I call removerow from Makesheet1, so you only have to run MakeSheet1.

You want 50 days of data after removing the blanks?

OK, however you don't have 50 days worth of data on sheet 5. Send back a modified sheet. I have an idea.

OK, time for something completely different. The assumption is that all the data on Sheet 5 is contained within the first 1,000 rows. BTW: I "hacked in more dates to do additional testing

I make use of a pivot table that I have on the Helper Sheet. The pivot tabled has the time stamp in Column A (which is a unique list of the time stamps). In this case there are 56 unique dates.

The results of the pivot table are "overlaid" with a named dynamic range called Date_List = OFFSET(Helper!$A$2,COUNTA(Helper!$A:$A)-52,0,50,1). This wiki explains named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges, but basically Date_List points to the last 50 dates in the pivot table.

Then it is a matter of copying these 50 dates into the table on sheet1. Since the pivot table contains only those dates that actually exist, there is no need to remove blank rows.

Please Login or Register  to view this content.