VBA to remove blak cells in a row and output values in consecutive cells

Hi,

I need a VBA code to remove blank cells within a row in another worksheet. Lets call the work sheets with the blank cells "WorkSheet1" I used IF statements to output a value or a "" but it starts on I10:I1000 and in some rows i only have 1 cell that has value. I want to use VBA on another work sheet to consolidate the non blank cells by outputting them consecutively in one row with no blanks from one value to the next.

Any help is greatly appreciated.

hey Hemesh,

Thanks for you quick response. the code works nicely but my data is laid out horizontally (in a row). Also, is there anyway not to use a toggle button? and have it rerun every time when the file is opened?

Sure thing. The data sheet has the raw data and the summary is where i want to organize and remove the blanks. The purpose is i have assemblies (cells in red) that is shared in both spread sheets; in the data sheet i have assemblies in the first column and parts 1-1000 in a row, as i am create each assembly i check what parts i need and the same assembly name is pulled to the summary sheet but in the summary sheet i only want to show the parts that is needed per assembly.