PDA

View Full Version : Find N, leave blank and move down



sasa
01-20-2013, 11:34 AM
Hi All,

This macro delete the cell containing the word in a cell and shift the rest of that column up.
Sub Find_N_MoveUp()
Dim rFind As Range
Dim rValue As String
Dim rFound As Range
Dim rRange As Range
Dim strFirstAddress As String

Set rRange = ActiveSheet.UsedRange
rValue = InputBox("Enter value to find", "Find all occurences")
With rRange
Set rFind = .Find(rValue, LookIn:=xlValues, lookat:=xlWhole)
If Not rFind Is Nothing Then
strFirstAddress = rFind.Address
Set rFound = rFind
Do
Set rFound = Union(rFound, rFind)
Set rFind = .FindNext(rFind)
Loop While Not rFind Is Nothing And rFind.Address <> strFirstAddress
End If
End With

If Not rFound Is Nothing Then
rFound.Delete Shift:=xlUp
End If
I am wondering if it is possible to modify this macro the way it delete the cell containing the word but leave blank that cell and shift the rest of that column down.

Thanks in advance

Sasa

GarysStudent
01-20-2013, 11:59 AM
This version will only Clear and not delete:

Sub Find_N_MoveUp()
Dim rFind As Range
Dim rValue As String
Dim rFound As Range
Dim rRange As Range
Dim strFirstAddress As String

Set rRange = ActiveSheet.UsedRange
rValue = InputBox("Enter value to find", "Find all occurences")
With rRange
Set rFind = .Find(rValue, LookIn:=xlValues, lookat:=xlWhole)
If Not rFind Is Nothing Then
strFirstAddress = rFind.Address
Set rFound = rFind
Do
Set rFound = Union(rFound, rFind)
Set rFind = .FindNext(rFind)
Loop While Not rFind Is Nothing And rFind.Address <> strFirstAddress
End If
End With

If Not rFound Is Nothing Then
rFound.Clear
End If
End Sub


This version will Clear and push down:

Sub Find_N_MoveUp()
Dim rFind As Range
Dim rValue As String
Dim rFound As Range
Dim rRange As Range
Dim strFirstAddress As String

Set rRange = ActiveSheet.UsedRange
rValue = InputBox("Enter value to find", "Find all occurences")
With rRange
Set rFind = .Find(rValue, LookIn:=xlValues, lookat:=xlWhole)
If Not rFind Is Nothing Then
strFirstAddress = rFind.Address
Set rFound = rFind
Do
Set rFound = Union(rFound, rFind)
Set rFind = .FindNext(rFind)
Loop While Not rFind Is Nothing And rFind.Address <> strFirstAddress
End If
End With

If Not rFound Is Nothing Then
rFound.Clear
rFound.Insert Shift:=xlDown
End If
End Sub

sasa
01-20-2013, 01:00 PM
thanks in advance, but what is cleared has to copy down as well. Any help ?

Sasa:rotlaugh: