Auto Filter out text or blank rows

[Guest]

What would I put into an autofilter parameter to show only rows which begin
with text or are blank?

 

[Guest]

Autofilter only works when there is data in continuous rows. If you have an
entirely blank row, Autofilter will not work the way you want. The way I've
gotten around this is to add a column and put in a row number or some other
data for ALL THE ROWS that I want to review. I'll then use the autofilter
to find the cells that are blank.

 

[Dave Peterson]

Ah, that's if you leave excel to its own devices.

If you select the whole range, then excel will include the all-blank rows in
that autofilter range.

(One of the reasons I select my range before I apply data|Filter|Autofilter (and
Data|Sort and Data|pivottable and....)

 

[Dave Peterson]

I'm not quite sure what you mean. Do you want to show the rows where the value
in column A begins with a letter (or column A is empty)?

You could use a column of helper cells with formulas like:

=OR(AND(CODE(UPPER(A2&"A"))<=90,CODE(UPPER(A2&"A"))>=65))

Then filter to show only the Trues.

 

[Guest]

Thanks for teaching me something today! I knew I didn't know everything!

 

[Guest]

Heres the deal, one of my suppliers emails me my order I've placed with them
over the phone. The only file they can send to me which has my costs in it
is an Excel version of the order....designed to be printed just like thier
paper order...with headings (where column A is blank and accounts for about
20 rows of data) and categories (which always have text in column A). I'm
attempting to create a complex macro which gets rid of all the junk (rows
with headings & categories) to get to a comma delimited text file for
importing into an inventory management database.

The total number of rows in any given order is variable, therefore, I'm
trying to set a macro to get rid of the junk and keep the important data.
Does this help?

 

[Dave Peterson]

So if there's anything in column A, you can delete that row?

If yes, convert column A to values--just in case it's a formula:

Option Explicit
Sub testme()

Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("a:a")

With myRng
.Value = .Value
On Error Resume Next
.Cells.SpecialCells(xlCellTypeConstants).EntireRow.Delete
On Error GoTo 0
End With

End Sub

 

[Guest]

Actually, its only if there isn't a number in column A that I want to delete
the row. Rows where column A is blank or contains text should be deleted.

 

[Dave Peterson]

How about this:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, "A")) _
Or IsNumeric(.Cells(iRow, "A").Value) = False Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Actually, its only if there isn't a number in column A that I want to delete
the row. Rows where column A is blank or contains text should be deleted.