Delete rows, then reorganise remaining data

**Steve_123** · 01-10-2014, 02:52 AM

Hi there

I'm hoping someone can help me delete some rows from a worksheet, then reorganise a lot more.

I've got an exceptionally slow way of doing it at the moment with millions of lines of very bad code from just recording various steps but it crashes my computer every time so I'm hoping there's a quicker way.

I've attached a sample document for what the information looks like now and what I'd like it to look like afterwards.

I've used two sheets for clarity but ideally would like the action to happen over the top on the one sheet. This is not essential though.

There are three members in the sample but in reality there could be 100s - it will vary each time. The data in A will always follow the same pattern, but in B there may be the odd field that is missing information.

I'm hoping to have the information in A18:B20 deleted, to follow in this way for each member, so deleting the rows relating to the below, then reorganising the remaining info as per the After sheet.

Type
Last Viewed
Last notified

Thanks as ever for your help.

Steve

**JBeaucaire** · 01-10-2014, 03:15 AM

With consistently laid out data, VBA isn't really needed. Drop the new data into NOW, then these formulas on AFTER will reorganize it:

A2: =INDEX(Now!A:A,ROWS($A$1:$A1)*21-20) (copied down until blanks appear)

B2: =IF($A2="", "", INDEX(Now!$B:$B, MATCH($A2, Now!$A:$A, 0)+COLUMNS($A$1:A$1))&"") (copied down and across the remaining table).

Once all the values are showing, you can highlight the data, then COPY > PASTE SPECIAL > VALUES onto a 3rd sheet to save it permanently with no formulas, leave your other two sheets setup to keep giving you results when you drop new data onto the first sheet.

**:) Sixthsense :)** · 01-10-2014, 04:03 AM

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Please Login or Register  to view this content.

**Steve_123** · 01-10-2014, 04:08 AM

You are an amazing man Jerry and have saved me literally hours if not days! For your amusement I'll post the woeful home made code I was using to achieve the same thing. It locked up my laptop every time!

Please Login or Register  to view this content.

Delete rows, then reorganise remaining data

LinkBack

Thread Tools

Rate This Thread

Display

Delete rows, then reorganise remaining data

Re: Delete rows, then reorganise remaining data

Re: Delete rows, then reorganise remaining data

Re: Delete rows, then reorganise remaining data

Thread Information

Users Browsing this Thread

Similar Threads

Code will delete first 2 rows but not the remaining 10

Find data and delete remaining rows (For each loop)

[SOLVED] Macro to delete sheets and saves remaining file does not properly delete module

Re: Macro to delete sheets and saves remaining file does not properly delete module

[SOLVED] Re: Macro to delete sheets and saves remaining file does not properly delete module

Bookmarks

Bookmarks

Posting Permissions