Selecting rows based on a value
 

I have a large spreadsheet that I need to sort on a daily basis. I have
several macros that I use to sot and delete rows but I can't figure out how
to do one thing.

The rows all contain data based on warehouse locations. The first column of
the sheet lists all of the warehouses based on letter designations (A,B,C...)
I want to select the rows containing data related to warehouse "A" and delete
everything else. Any suggestions?

--
Whether you think you can or cannot, you're right. - Henry Ford

Selecting rows based on a value
 

On Nov 19, 7:14*am, Jeff Parrott
wrote:
I have a large spreadsheet that I need to sort on a daily basis. I have
several macros that I use to sot and delete rows but I can't figure out how
to do one thing.

The rows all contain data based on warehouse locations. The first column of
the sheet lists all of the warehouses based on letter designations (A,B,C....)
I want to select the rows containing data related to warehouse "A" and delete
everything else. Any suggestions?

--
Whether you think you can or cannot, you're right. - Henry Ford

Sub RemoveAllButA ()
Dim i
For each i in range("A:A")
If i < "A" then
i.entirerow.delete
Else
goto e
End if
E:
Next i
End Sub

This is untested, but should get you in the right direction.
Jay

Selecting rows based on a value
 

This should do the trick
Sub Remove_Unwanted_Rows()
'his deletes all rows with cells not matching cases
Const whatColumn As String = "A"
Dim rng As Range
Dim Cell, RowArray As Range
Set rng = ActiveSheet.Range(Cells(1, whatColumn), _
Cells(Rows.Count, whatColumn).End(xlUp))
For Each Cell In rng
Select Case Cell
Case Is < "A" '& any others you like separted by commas
If RowArray Is Nothing Then
Set RowArray = Cell.EntireRow
Else
Set RowArray = Union(RowArray, Cell.EntireRow)
End If
End Select
Next Cell
On Error Resume Next
Debug.Print RowArray.Address
RowArray.delete
Err.Clear
End Sub

"Jeff Parrott" wrote:

I have a large spreadsheet that I need to sort on a daily basis. I have
several macros that I use to sot and delete rows but I can't figure out how
to do one thing.

The rows all contain data based on warehouse locations. The first column of
the sheet lists all of the warehouses based on letter designations (A,B,C...)
I want to select the rows containing data related to warehouse "A" and delete
everything else. Any suggestions?

--
Whether you think you can or cannot, you're right. - Henry Ford

Selecting rows based on a value
 

Jeff,

You have to do it the other way around and select rows that don't relate to
A and then delete.

Right click your sheet tab, view code and paste this in and run it. Because
it deletes data try it on a test workbook.

Sub Marine()
Dim MyRange As Range, copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If ucase(c.Value) < "A" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike

"Jeff Parrott" wrote:

I have a large spreadsheet that I need to sort on a daily basis. I have
several macros that I use to sot and delete rows but I can't figure out how
to do one thing.

The rows all contain data based on warehouse locations. The first column of
the sheet lists all of the warehouses based on letter designations (A,B,C...)
I want to select the rows containing data related to warehouse "A" and delete
everything else. Any suggestions?

--
Whether you think you can or cannot, you're right. - Henry Ford