Using selected ranges
 

Hello,

Im tryin to get a sub or macro that will search a selection(in
column) and if the individual cells meet a critieria I want to delet
the rows. The part Im havin trouble with is working with the selecte
area.

I wrote this one which works if I select the cells individually:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/25/04 by Enter Your Name Here
'
Dim A As String

A = ActiveCell

If A = "FEES" Then


Selection.Delete Shift:=xlUp

End If
End Sub


What do I do to make it work with a selection

--
Message posted from http://www.ExcelForum.com

Using selected ranges
 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/25/04 by Enter Your Name Here
'
Dim A As String, first as Long, last as Long

if selection.Areas.Count 1 or selection.Columns.count 1 then
msgbox "Select single column only, 1 area"
exit sub
End if
first = selection(1).Row
last = selection(selection.count).Row

for i = last to first step -1
If cells(i,selection.column).Value = "FEES" Then
cells(i,Selection.Column).Delete Shift:=xlUp
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"ksnapp " wrote in message
...
Hello,

Im tryin to get a sub or macro that will search a selection(in a
column) and if the individual cells meet a critieria I want to delete
the rows. The part Im havin trouble with is working with the selected
area.

I wrote this one which works if I select the cells individually:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/25/04 by Enter Your Name Here
'
Dim A As String

A = ActiveCell

If A = "FEES" Then


Selection.Delete Shift:=xlUp

End If
End Sub


What do I do to make it work with a selection?


---
Message posted from http://www.ExcelForum.com/

Using selected ranges
 

Hello,

This does the trick

Sub DeleteEntireRowWithSpecialValue()
Dim rngFirst As Range
Dim rngLast As Range
'
' We step throu the selection one row below
' the row that will be tested for deletion
' otherwise we loose the control of rngFirst
Set rngFirst = Selection.Cells(1).Offset(1, 0)
Set rngLast = Selection.Cells(Selection.Cells.Count).Offset(1, 0)
If rngFirst.Row = rngLast.Row Or rngFirst.Column < rngLast.Column Then
MsgBox "Use a series of rows, one column width"
Else

Do
If rngFirst.Offset(-1, 0).Value = "FEES" Then
rngFirst.Offset(-1, 0).EntireRow.Delete
Else
Set rngFirst = rngFirst.Offset(1, 0)
End If
Loop Until rngFirst.Row = rngLast.Row
End If
End Sub

Wouter Magre.


ksnapp wrote in message ...
Hello,

Im tryin to get a sub or macro that will search a selection(in a
column) and if the individual cells meet a critieria I want to delete
the rows. The part Im havin trouble with is working with the selected
area.

I wrote this one which works if I select the cells individually:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/25/04 by Enter Your Name Here
'
Dim A As String

A = ActiveCell

If A = "FEES" Then


Selection.Delete Shift:=xlUp

End If
End Sub


What do I do to make it work with a selection?


---
Message posted from http://www.ExcelForum.com/

Using selected ranges
 

very helpful thank yo

--
Message posted from http://www.ExcelForum.com