Help with an Excel Macro

[thelke]

I am trying to take specific cells out of an excel sheet. by these lines of code, it should work..at least so I thought

Sub Test()
Columns("C:C&quot.Select
Selection.Delete What:="TextHere", Shift:=xlToLeft, LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Please help

 

[Gorth]

Hi,

The .Delete Method does not support any arguments except for the direction that you want to move.

As it is in the help file:
expression.Delete(Shift)

Here is an alternative:
Sub Test()
Application.ScreenUpdating = False ' Prevents flickering of Excel and runs faster
lRow = Range("C65536&quot.End(xlUp).Row 'Determines last row of data in sheet
For Each x In Range("C1:C" & lRow)
If x.Value = "TextHere" Then
x.Delete (xlToLeft)
End If
Next x
End Sub

Thanks!
-Gorth

 

[Loomah]

Hi
This is just another alternative. This should be quicker than iterating all cells. I've just tested it on 10000 rows and it took little over 12 seconds to delete ~100 instances of whatever.

Sub s()
Dim lRow As Long
Dim c As Range
lRow = [c65536].End(xlUp).Row
With Range("C1:C" & lRow).Cells
        Do
            Set c = .Find(10, LookIn:=xlValues)
            If c Is Nothing Then Exit Do
            c.Delete (xlUp)
        Loop
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[LokiOfGameSlave]

If you are going to use Gorth's method make sure to add "Application.ScreenUpdating = True" to the end of the macro to turn ScreenUpdating back 'on'.

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Gorth]

Actually, "Application.ScreenUpdating = True" is not needed, it gets turned back on at the end of a procedure anyway.

-Gorth

 

[Zathras]

thelke: ScreenUpdating does not get turned back on at the end of a procedure. It gets turned on at the end of the top level procedure which may or may not be sufficient depending on how much control the macro is asserting. As a matter of good programming practice, you should always turn ScreenUpdating back on whenever you have turned it off.

For example, consider the following:
[blue]

Sub Test()
  Range("B:B").Clear
  UpdateCells
  MsgBox "What do you see?  (Column B appears blank, but isn't.)"
End Sub

Sub UpdateCells()
  Application.ScreenUpdating = False
  For i = 1 To 1000
    Cells(i, 2) = Cells(i, 2) + i
  Next i
End Sub

[/color]
Position the VBA editor in such a way as to allow column "B" of the worksheet to remain visible, then run the "Test" macro.