Selecting and removing a range of data using VBA

[9littlebees]

I have a range of data which is repeated after a break in row X. I need help setting up a VBA macro to select the top range of data and remove it.

This is exported data from another programme and one worksheet contains both "pre" and "post" data for the same data set, displayed as mentioned above.

I need to seperate the two data ranges so that they are on seperate worksheets. This data is exported monthly, and the row with the break in it changes depending on the number of fields that month, so I cannot use a macro which specifies an actual row number.

I am using Excel 2000 and 2003.

Any help would be hugely appreciated!

 

[BrianB]

Need to record a macro to get the code and then auto-change the ranges depending on some sort of uniqueness in their content. eg. a column or row heading or whatever.

 

[9littlebees]

Thanks for the reply, Brian. I'm afraid I'm not really following you, though...

Here's roughly what the spreadsheet looks like:

A ID Data1 Data2 Dataz
Pre 320 x x
415 x x
520 x x
(blank row)
Post 320 y y
415 y y
520 y y

So I want to get that data to be split between two sheets, then delete column A.

What would I be auto-changing?

 

[Peter_SSs]

Welcome to the MrExcel board!

Test this on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SplitData()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsPre <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsPre = ActiveSheet<br>    wsPre.Columns("A").Delete<br>    wsPre.Copy After:=wsPre<br>    ActiveSheet.Range("A1").CurrentRegion.Offset(1).EntireRow.Delete<br>    wsPre.Range("A" & Rows.Count).End(xlUp).CurrentRegion.EntireRow.Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[9littlebees]

Hi Peter,

Thanks very much for the reply! I've tested this on my workbook - and it works like a charm!

I have another query reagrding the same troublesome workbook, but I'll post that in another thread if I can't find an existing one to provide guidance.

It has to do with looking up the Status of all the IDs on the first sheet, and when the Status for any ID is "Closed", all the rows containing that ID across the entire workbook will be deleted. This means that I am left with a workbook that only contains "Open" data.

Anyway, thanks again for your help.

 

[9littlebees]

The new thread (mentioned in my previous post) is at:

http://www.mrexcel.com/forum/showthread.php?p=2389187