Global Delete - two questions

[Guest]

Question One

I have five columns and 80 pages of data. The third column contains 4
numbers.
I would like to delete the row/record for every line of data which contains
an odd number as the fourth number in his column. I'd like to do this
globally rather than one line at a time. Is there a way.

Question Two

The same 80 pages of five column rows contain contain many rows without any
data in any of the five cells. How can I delete those "empty" lines and
create a document that has only lines with data?

Thank you.

 

[Gord Dibben]

Stumped

Question One..............

Tools>Add-ins.

Load the Analysis Toolpak.

In F1 enter =ISODD(LEFT(C1,4))

Copy down as far as you have data in column C.

Will return TRUE or FALSE

Autofilter for TRUE then delete those records.

Question Two................

Would there be blanks in other cells as well as just entire rows?

If not, select column A and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

If a row has some blanks but not the entire row, you could use a macro to find
and delete just those rows that are entirely blank.

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord:

I'm afraid that you lost me somewhere.
I open an EXCEL document.
I click on Tools>Add-ins.
I check the Analyses Toolpak and click OK
I'm not sure what is happening at this point but there is
no indication of an F1 where I can enter the "=ISODD(LEFT(C1,4))
as you instructed.
Therefore I cannot attempt to execute the rest of your instructions.
Please advise.
Thanks

 

[Guest]

Hi Gord:

I've been playing with this for a couple of hours and simply do not
understand the proceedure you are describing. When you refer to F1, are you
referring to the title box of the F Column which is the next blank column?
I've done that and when I switch to another column, the name of the other
column comes up in the Column, but there is no where to go from there. No
messages come up on the screen.

When you say "copy down", do you mean Block Copy all the numbers in Column C?
If so, when/where does the TRUE or FALSE option appear? And where is the
autofilter screen?

 

[Gord Dibben]

F1 is a cell address and you enter the formula in that cell.

To copy that formula down, hover the cursor over the righthand bottom corner of
F1 cell.

You will see a small black cross appear.

Click on the cross then hold and drag down column F as far as you have data in
column C

I don't know how far that would be because "pages" doesn't mean anything unless
you are printing.

You should get used to referring to sizes as number of rows or columns. not
pages.

Let go the mouse button.

You will get TRUE or FALSE in the cells of column F if you have calculation set
to automatic.

Select column F and Data>Filter>Autofilter.

Click on the arrow in F1 and select to filter on TRUE.

Then hit the F5 frunction key and >Special>Visible cells only and OK

Edit>Delete>Entire Row.

Data>Filter>Autofilter again to clear the dropdown.

As far as the other question goes............get this first question cleared up
then post back.

You might take time out to visit one or more of these sites.

Some tutorial sites for basics of Excel........

http://www.usd.edu/trio/tut/excel/index.html

http://www.baycongroup.com/el0.htm

http://office.microsoft.com/en-us/training/CR061831141033.aspx


Gord