PDA

View Full Version : Solved: move up



sasa
06-18-2008, 11:24 PM
Hi All,
I ask if it is possible in a way similar this macro:

sub Del_Rows()
Dim MyCell As Long
Dim i As Long
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i).Value = "Data" Then Cells(1, i).EntireColumn.Delete
Next i
For MyCell = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Application.WorksheetFunction.CountIf(Rows(MyCell), "Happy") >= 1 _
Or Application.WorksheetFunction.CountIf(Rows(MyCell), "Angry") >= 1 _
Then Rows(MyCell).EntireRow.Delete
Next MyCell
End Sub

to substitute the delete stuff with a move up option. In other words to move up the under list of items in the considered columns after deleting the two words.

Thanks in advance

sasa

mikerickson
06-19-2008, 12:47 AM
You might want to provide a link to the thread where that code came from.
This will delete the cells containing "Happy" and "Angry" rather than the whole row
Then Rows(MyCell).Delete shift:=xlUp

sasa
06-19-2008, 01:21 AM
ok, thanks, but I don't want delete the cell, but only clear it, and after move up.

thank you again

Simon Lloyd
06-19-2008, 01:28 AM
Sasa, the code that you got from this thread (http://vbaexpress.com/forum/showthread.php?t=20255) was to do a specific job, however just replace .Entirerow.Delete with .Entirerow.clearcontentsthen do a sort or delete the balnk rows and move up!

P.S when posting code please use the code tags.

Simon Lloyd
06-19-2008, 01:33 AM
Or better still Rows(MyCell).clearcontents

sasa
06-19-2008, 01:49 AM
Be patient, pls..
I don't want clear the entire row, but only the cells where are the two words, and after move up the list below.

Thanks again

Simon Lloyd
06-19-2008, 02:29 AM
Then you dont want to clear contents you want to delete the cell containing the word and shift the rest of that column up.

sasa
06-19-2008, 03:15 AM
yes, you are right.. but only the cells containing the words and not the entire row. My mistake was linked to the idea that only after clearing the content of the cells , it was possible to shift the rest of that column up in the blank spaces.

Thanks
sasa

Simon Lloyd
06-19-2008, 03:36 AM
This is what you want:

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
End Sub

sasa
06-19-2008, 04:28 AM
Really thanks.
sasa