Excel 2003 sort doesn't take cell borders along with data 2

[CarpalT]

They just upgraded my pc to Office 2003. I periodically obtain a download from a mainframe that has a line of data, then a blank line, then a line of data, on and on for hundreds of records. I have always sorted this on one of the fields and that puts all the blanks in one area where I delete them. With the new excel, the occasional subtotals, indicated by top & bottom border on the cells in that row, do not "go along for the ride". So I'm looking for a setting change to fix that, or an alternate suggestion that I can use to delete every blank row in about 500 rows of data.
I only have to do this once or twice a month, so it's never been worth writing a macro, and I hope that's still the case!
I would appreciate any suggestions you may have. Thanks!

The world is full of good people.

 

[AnotherHiggins]

If there is a column that is always blank in all rows you want to delete (which it sounds like there is), then I think it is totally worth a macro - especially since it is so short. This is all you need:

Sub DeleteRowsFromMonthlyFeed
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Just replace column A with whatever column you need.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

If you don't want to use code, then simply pick a field that you can 100% rely on to have data in if you were to keep the row, select that column, do Edit / go To / special / blank cells / and then do Edit / delete / entire row.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CarpalT]

Thank you both for your attention to my problem. I can't make either one of these solutions work, but it's not your fault! I should have at least five columns that never have a blank, so started out hopefully. Although I can't find them, there must be a handful of blanks in every one of them, because I lose just a few rows with both methods.
However, I learned something from both posts and am grateful for that.
Thanks a million!

The world is full of good people.

 

[KenWright]

No problem - I'm guessing that every record you want to keep has at least one element of data in it though, so create a helper column at the end of your data (I'll assume your data is in Cols A:J starting row 2).

In K2 put the following:-

=COUNTA(A2:J2)=0

Copy down as far as your data goes, select that column and do Data / Filter Autofilter and filter on TRUE. Now select the data in that column, do Edit / Go To / Special / Visible cells only, and then do edit / Delete / Entire Row.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CarpalT]

Great - works like a champ! I learned three things today thanks to you, John and Ken . Have a great day!

The world is full of good people.

 

[KenWright]

You're welcome

Just as an aside, if you had a dataset and for whatever reason wanted to lose every other row, then you can follow the instructions I gave you but use

=MOD(ROW(),2)<>0

or

=MOD(ROW(),2)=0

depending on which way round you want your TRUE/FALSEs.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CarpalT]

Thanks a million, I'll be able to use that sometimes. On the one I'm doing today, it is an exported report, so sometimes it throws in a few extra blanks before the next section, so it's not predictable after the first section, but I understand what you're saying.

The world is full of good people.

 

[AnotherHiggins]

And just to kick this dead horse one more time:

The code version of Ken's [COLOR=blue white]=COUNTA(A2:J2)=0[/color] is

Range("a1").EntireRow.Select
    With Selection
        Do
            If Application.WorksheetFunction.CountA(Selection) = 0 Then
            Selection.EntireRow.Delete
            Else
            Selection.Offset(1, 0).Select
            End If
        RowCount = ActiveSheet.UsedRange.Rows.Count
        Loop Until ActiveCell.Row >= RowCount
    End With

That will loop through all rows and get rid of all that are completely empty.

I keep the following around on a custom toolbar and I use it pretty frequently. It loops through all rows and columns and gets rid of any that are completely empty. Very hand, IMHO.

Sub DeleteEmptyRowsAndColumns()
'written by John Helsabeck

Application.ScreenUpdating = False

'the following grabs the current activecell
BegAddress = ActiveCell.Address

'delete empty rows
Range("a1").EntireRow.Select
    With Selection
        Do
            If Application.WorksheetFunction.CountA(Selection) = 0 Then
            Selection.EntireRow.Delete
            Else
            Selection.Offset(1, 0).Select
            End If
        RowCount = ActiveSheet.UsedRange.Rows.Count
        Loop Until ActiveCell.Row >= RowCount
    End With

'delete empty columns
Range("a1").EntireColumn.Select
    With Selection
        Do
            If Application.WorksheetFunction.CountA(Selection) = 0 Then
            Selection.EntireColumn.Delete
            Else
            Selection.Offset(0, 1).Select
            End If
        ColumnCount = ActiveSheet.UsedRange.columns.Count
        Loop Until ActiveCell.Column >= ColumnCount
    End With

'return focus to cell that was active before code was run
Range(BegAddress).Activate
Application.ScreenUpdating = True
End Sub

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[CarpalT]

You guys are making my little head hurt! I had to figure out how to make a vb macro in order to test drive them, but I've got them both running after a few false starts and some digging into online help and my big fat Excel book. I appreciate all the extra suggestions, it moved me to a new level within Excel. Again, thanks!

The world is full of good people.