Deleting Rows in Excel 3

[Haf]

I have a large set of data that I would like to filter. The data is arranged in rows, and I would like to delete certain rows. For example: I have a data set containing 40,000 rows of data. I would like to keep the first row, delete the following four, keep the next, delete the following four, and so on. I assume there is some easy way to accomplish this that is built into Excel. Can anyone help?

Thanks,

Haf

 

[DaleWatson123321]

Haf,

A couple of points...

1) Because you use the term "filter" which can involve "hiding rows" as opposed to actually "deleting" the rows, it is important for you to clarify WHICH of these two options you want - i.e. to HIDE or DELETE the rows.

2) Is there an existing "condition" that you can use to "identify" those rows (records) you want to hide or delete. For example, are these "blank" rows, or is there one field that is consistently "blank" but ONLY for those rows you wish to hide or delete. Or, if you are not dealing with "blanks", perhaps there is some other "identifier" which can be used - used as a "criteria" for hiding or deleting.

Finally, another option might be to "extract" those records you DO want INCLUDED - where you would extract them to a separate sheet.

All these options are possible, but it would help to have a bit more clarification...

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Haf]

Thanks for the quick reply.

Here's a better description of my problem. I am importing data from a computational code and then plotting the results in Excel. The code I am using requires that I import ALL of the computed data into Excel, which can be thousands or even hundreds of thousands of rows. The difference in the data between each row is small, since, in one time-step, not much happens in the code. Thus, for accurate plotting, I do not need all of the data, just, say, one in every 5, 10, 100, etc. rows, depending on the size of the file (Excel can only plot 32,000 points on a 2-D plot). Thus, I would like to permanantly delete these points (rows), both to reduce the overall size of the Excel file, and to allow for plotting in Excel. I

I should not have used the term "filter". In escence, I would like to "sample" the data. "Undersampling" the data occurs when you are sampling a data set too slowly to accurately capture and recreate that set. In my case, I have entirely too much data to handle in Excel, and as long as I am careful (i.e., as long as I don't remove too much data), I will not undersample, and the plots I create in Excel will accurately reflect the computed data.

If need be, I can write a computer code to reduce my file size as I specify, but I thought that there may be an easy way to do this in Excel.

Thanks,

Haf

 

[SkipVought]

Hi,
If you want to import a fraction of you file based on x out of y records (assuming that the file has been properly sorted), Open your file for Input, and Input or Line Input x out of y records. You will have to be sure that you do not exceed the 65,536 row limit. Skip,
metzgsk@voughtaircraft.com

 

[binky]

I apologize up front that I'm not giving you the actual code for this because I haven't figured it out yet. But...

1. Try creating a button on a form and use it as the trigger to 'Sample' your data.

2. In this button find out the total number of entries in you import page.

3. Use this as the upper limit of an array.

4. Loop through the array and if the index number is evenly divisable by five then copy that row into another worksheet.

I hope this helps. If you need any help let me know.

Ken :

 

[DaleWatson123321]

Haf,

Skip's point about having to limit the number of records imported to <65,536 is certainly a VALID one.

But if you bring in a &quot;chunk&quot; at a time (up to the 65,536), then it will be possible to have Excel eliminate records exactly as you described - i.e. leave the 1st, delete the next 4, leave the next, delete the next 4, etc.

I've created a WORKING model - which extracts the required records from one sheet to the next. It's already set up with an &quot;identifier&quot; for use by &quot;criteria&quot; to extract the data you require. It also includes a &quot;button&quot; to which the VBA extraction code is attached.

If you would like the file, simply email me, and I'll return the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Haf]

Binky,

Thanks for the pointers. Actually, your suggestions are very similar (basically identical) to what I planned to do with a computer code of my own. I had planned to open and read the file and then write a new, reduced file. Unfortunately, I don't know how to do this type of programming in Excel. If you have time, I'd appreciate help. I've always used programs like C and Matlab to do this sort of thing, but I guess it would be useful to know how to do this in Excel.

Thanks,

Haf

 

[dsi]

If you want to use a macro, try this:

Sub RemoveExcessEntries()
    Dim iRow As Long
    iRow = 1    'first row of data
    Do While Len(Range("A" & iRow).Text) > 0
        iRow = iRow + 1
        Rows(iRow & ":" & iRow).Select
        Selection.Delete Shift:=xlUp
        Selection.Delete Shift:=xlUp
        Selection.Delete Shift:=xlUp
    Loop
End Sub

It will remove 3 out of every four rows until it reaches the end of the data in column A.

Hope this helps!

 

[binky]

dsi,

What a great piece of code. I had racked my brain trying to figure out how to get the upper limit of the loop.

What I am curious about is whether the iRow incrementer should be at the end within the loop?

Binky :

 

[Haf]

Many thanks to Dale Watson and dsi. Dale's extraction code works beautifully. dsi's code works perfectly, but runs too slowly to be of use with thousands of rows of data.

Overall, though, I have come to the conclusion that it is most effective to write a simple code (outside of Excel) to perform the file reduction. Nonetheless, I greatly appreciate everyone's help!

Haf