D
D Hafer - TFE
What would I put into an autofilter parameter to show only rows which begin
with text or are blank?
with text or are blank?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Barb R.
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.
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
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....)
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....)
D
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.
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.
B
Barb R.
Thanks for teaching me something today! I knew I didn't know everything!
D
D Hafer - TFE
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?
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?
D
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
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
D Hafer - TFE
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.
the row. Rows where column A is blank or contains text should be deleted.
D
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
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.