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

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.

"D Hafer - TFE" wrote:

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

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....)



Barb R. wrote:

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.

"D Hafer - TFE" wrote:

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



--

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.

D Hafer - TFE wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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....)



Barb R. wrote:

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.

"D Hafer - TFE" wrote:

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



--

Dave Peterson

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" wrote:

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.

D Hafer - TFE wrote:

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

--

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



D Hafer - TFE wrote:

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" wrote:

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.

D Hafer - TFE wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote:

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



D Hafer - TFE wrote:

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" wrote:

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.

D Hafer - TFE wrote:

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

--

Dave Peterson


--

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



D Hafer - TFE wrote:

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" wrote:

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



D Hafer - TFE wrote:

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" wrote:

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.

D Hafer - TFE wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson