Using selected ranges

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

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
 
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
 
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.
 
Back
Top