Delete Cells 1

[sdrp]

Hi,

Does anyone know the VB to delete all cells which have a certain criteria (eg "Hello&quot to the left?

Cheers

 

[dyarwood]

Do you want to delete or clear the contents? If you want to delete is it just the cells with "hello" in the next cell left or is it the row? In which direction do you want the cells to move once deleted if just the cell?

dyarwood

 

[sdrp]

Actually delete all cells with "Hello", in say column A, after that move the other cells to the left.

cheers

 

[dyarwood]

i = 2 'Row index

Do Until IsEmpty(Cells(i,1))
If Cells(i,1).Value = "Hello" Then
Cells(i,1).Delete Shift:=xlLeft
Else i = i+1
Loop

Try that. Will loop down until the cell is empty and will not move down if the cell is deleted incase the cell which has just moved there contains hello.

dyarwood

 

[Loomah]

Hi sdrp
This will delete all cells in col A that contain "hello" and shift the cells left. If the new cell (after the shift) contains "hello" it is ignored.

Sub b()
Dim lRow As Long, lCnt As Long
lRow = [A65536].End(xlUp).Row
For lCnt = lRow To 2 Step -1
    If Cells(lCnt, 1) = "hello" Then
        Cells(lCnt, 1).Delete Shift:=xlToLeft
    End If
Next
End Sub

To allow for numerous "hellos" in a row you'll neeed an adaptation of dyarwood's code like so

Sub a()
Dim i
Dim lRow As Long
lRow = Range("A65536").End(xlUp).Row
i = 2 'Row index
Do While lRow >= i
    Do Until IsEmpty(Cells(i, 1))
        If Cells(i, 1).Value = "hello" Then
            Cells(i, 1).Delete Shift:=xlToLeft
        Else: i = i + 1
        End If
    Loop
    i = i + 1
Loop
End Sub

There probably is a quicker and easier way to do this using the Find method but I'm a bit pressed at the mo'

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[sdrp]

magic!!

cheers guys!!